I am working on a database that keeps track of several Production facilities as well as the contact information for two key positions in each facility: Plant Manager and QC Manager. Each plant will have one and only one Plant Manager and each Plant manager will be assigned to one and only one plant. The same is true for QC managers.
Initially I felt like I should have 2 different tables (facilities and managers) but am finding a little bit awkward to try to setup the data entry portion of the one-to-one relationship in a way that is intuitive to the user. Would it be more effective to do away with the Managers table all together and store all manager data (name, phone number, fax, email address) to the facility table like so:
tblFacility
- FacilityName
- FacilityAddress
- FacilityRegistrationNumber
- FacilityPlantManagerName
- FacilityPlantManagerPhone
- FacilityPlantManagerFax
- FacilityPlantManagerEmail
- FacilityQCManagerName
- FacilityQCManagerPhone
- FacilityQCManagerFax
- FacilityQCManagerEmail
Or am I on the right track by keeping them in two seperate tables? If so, how should I establish the relationship between these two tables?
Thanks for your thoughts,
Bruce