Equipment Calibration Database Relationships. (1 Viewer)

Hi,

I'm developing an equipment calibration database. I am playing with the following structure;

TBL_Site
LocationID-PK
Location _Name
Etc...

TBL_Equipment
EquipmentID-PK
Equipment_Desc
Serial_Id
Etc...

TBL_Site_Equipment (The link in Many:Many)
Link_LocationID-PK?
link_EquipmentID-PK?

TBL_Inspect_Record
InspectID-PK
LocationID-Composite Key
EquipmentID-Composite Key
Condition
Inspect_Date
Etc...

TBL_Measurement
MeasurementID-PK
Inspect_ID-FK
Actual_Measurement
Etc....

Is this sensible? Appreciate any feedback before I go off the rails by too much.

Without knowing the underlying business process, there are some questions. Is the Site-Equipment table required? Surely a specific item of equipment can only be in one location? Unless you move the serialID to that table?

Also not sure about the composite key - if that prevents duplicates, you can only do one inspection - think that needs to be removed, add a PK to your Site-Equipment and link to that

You may get some ideas from this thread

I agree with CJ, the only reason for having a site-location table is if you move things around and you want to keep history of where an item has been in the past.

But to answer your question about the PK for a junction table - the PK can be a 2-field key including both of the foreign keys. Even if you add an autonumber (I don't unless the junction table has child tables), you would still need a unique index on the two foreign keys.

If this junction table were to log history, a third field in the PK would be required and that would be something like "install date". Then you would have a couple more fields such as uninstall date and possibly disposition (scrap)

People, A big thanks. I've changed by database structure as per your combined recommendations.