Hi.
I'm fairly new to database design and I have a question about relating some tables together.
Let me give you an example to help explain my question. Let's say we have a table that describes a whole process, in this instance, every time one our shop vehicles goes to our auto shop to have an inspection done. So we have a table called "VehicleInspections". It has fields like ID, vehicleID, inspectionStartTime, inspectionStopTime, which maintenance bay it's in, etc. Now, let's say we have another table called "MaintenanceActions" that list all the maintenance actions done on a particular vehicle (discovered during the vehicle inspections). So this has fields like ID, vehicle Inspection ID (foreign key from ID in VehicleInspections), vehicleID, status of the action, remarks, etc. Lastly, let's say we also have a table that lists the vehicle IDs for all of our vehicles. Now, if I setup a lookup in table VehicleInspections in the vehcileID field to the VehicleIDs table, should I do the same in the MaintenanceActions table? Or should I/can I link the vehicleID field in MaintenanceActions to the vehicleID field in VehicleInspections (which is a lookup of the field in VehicleIDs)?
Hopefully I didn't lose you.
Basically, if I have table with a list of actions that is attached to a list of processes, should I link the thing they have in common (vehicleID) in each table to the lookup list, or just one to the lookup list and the other one back to the one that refers to the lookup list.
Keep in mind, this is just an example to explain my question. Also, not sure if it matters, but I will be not using the Access program to read and write the data.
Thanks for your help,
Jason