hi all, I'm trying to design a database that keeps track of some industrial machinery and the inspections that are made on them. I think I have the tables right, but a couple of the relationships I'm not so sure about... The tables are
tblMachine - each Machine belongs to only one client, and has many components
tblComponent - related to one Machine
tblInspection - the tricky part....this is the monthly inspection on each Machine
I'm not sure how to relate tblInspection to the others...an inspection is (currently) something that is done to a Machine (and all components thereof), giving a 1-M [Machine-Insp.] and a M-M [Comp.-Insp.], but it seems more logical to have the inspection as a 1-M with the component instead.....which then makes it a 1-1 between Inspection and Machine. Should I even have all three tables linking each other, or just two links?
The main goal of this dbase is to produce a report that shows, for each Machine, a list of its components (columns) and data for each components inspections (rows). Also, each component can be replaced, but the inspection history shouldn't worry about that (ie. the history should go back to the beginning of the machine, not the beginning of that specific component), so maybe I need another table to differentiate between a machine's component, and the specific item that is currently installed as that component on that machine?
I have some issues with how I'm gonna do this report, but I guess now I should concentrate on getting the design right....so any suggestions? All help is much appreciated thanks!
It isn't correct to say that making Inspection 1-M with Component "makes it a 1-1 between Inspection and Machine". It could be like this:
Machine --< Component >-- Inspection
i.e. Inspection has a M-M relationship with Machine.
In fact I think there is a missing entity here: you have the Inspection, of which there is 1 per Machine per month, but you also need the "Inspection-Component" entity that has the results of the Inspection for a particular Component. So a crude diagram would be: