Yes you can. Congratulations, you have come upon the central idea of
relational database design. Not only can you, but you should -- as long
as it accurately models your business process. Let me explain.
I was asked to do some reporting from an old database created years ago. It
was a record of patient sedations (that is a patient was given a sedative to
put them to sleep for some procedure). The business rules were these:
Each patient can have many procedures
Each procedure can have one or more medications (sedatives)
Each medication could have one or more complication
Each complication can have one or more interventions
The programmer (a consultant) created a Patient table with an autonumber
PatientID. He placed this primary key into each of the other four tables to
create relationships. Fine. It was relational. The problem was that I
could tell that a patient had so many procedures, medications,
complications, and interventions, but could not see WHICH procedures had
WHICH medications and so forth.
What he should have done was put the PatientID in the Procedure table, the
ProcedureID in the Medication table, the MedicationID in the... well, you
get the idea.
In the end, we had to dump ALL of the data and start over. None of it was
usable since the relationships between the tables were what they really
wanted to know.
So the lesson here is that just because you have created relationships
doesn't mean they are the right ones.
