Table layout help required!!!

[VinylMatt]

This might be simple or it might not...

I've got a table which stores items of equipment. Some items of equipment need servicing and they might need more than 1 type of service. So far I've come up with:

Table Name: [Equipment], PKey: [ItemID], Other fields as appropriate...
Table Name: [ServiceType], PKey: [ServiceTypeID], Description, Frequency
Table Name: [Service], PKey: [ServiceID], FKey: [ItemID], FKey: [ServiceTypeID]

Now what I want to do is store the results for each type of service in its own table (each service type will have different checks and therefore different results):

Table Name: [ServiceResultsForServiceTypeID1], PKey: [ServiceResultsID], FKey: [ItemID], DateTested, Results as appropriate...

Table Name: [ServiceResultsForServiceTypeID2], PKey: [ServiceResultsID], FKey: [ItemID], DateTested, Results as appropriate...

Now how do I join these result tables to the rest of the database? I thought about storing the table name for the results in a field in the ServiceType table but don't see how this'll work.

I hope I'm clear enough - let me know if this doesn't make sense.
Thanks in advance,
Matt.

[ronverdonk]

1st. what is the reationale of having a separate 'ServiceResultsForService' tabel for each servicetype?
2nd I have no intention of patronizing, but let's make it easier and visualise the tables involved. That way it is easier to see relations and connections between tables and keys. Example:

  Equipment         ServiceType             Service               Service Results
+-----------+    +---------------+    +---------------------+    +------------------+
| ItemID    |    | ServiceTypeId |    | ServiceId           |    | ServiceResultsId |
|  PKEY     |    |   PKEY        |    |  PKEY               |    |    PKEY          |
|-----------|    |---------------|    |---------------------|    |------------------|
| other ... |    | Descr, freq...|    | ItemId        FKEY  |    | ItemId    FKEY   |
|-----------|    |---------------|    |---------------------|    |------------------|
|           |    |               |    | ServiceTypeId FKEY  |    |                  |
|-----------|    |---------------|    |---------------------|    |------------------|
|           |    |               |    |                     |    |                  |
+-----------+    +---------------+    +---------------------+    +------------------+

Now you are able to draw (with a pen) those connections. If you cannot connect a table to any of the other ones, you must insert a field in that table that will make that possible.

Get back when you are done.

Ronald :cool:

[comteck]

Just two question.
1. What data type are you using for your Primary Keys and Foreign Keys in each of the tables?
2. What kind of relationship is set up between the tables?

comteck

[cleaner7777]

Just a guess... but you might try creating a view on top of all of your tables.

CREATE VIEW ServiceResultsForServiceType as
(SELECT * FROM ServiceResultsForServiceTypeID1)
UNION
(SELECT * FROM ServiceResultsForServiceTypeID2)
UNION
...
UNION
(SELECT * FROM ServiceResultsForServiceTypeIDn)