Relationship advice

[sstasiak]

I have a main table with a MedRecordNum as the primary key. Each MedRecordNum can have multiple treatment regimens, and each regimen can have multiple treatment dates.

I'm just wondering if I'm setting the relationships properly. I'm going to set MedRecordNum to have a one to many relationship with RegID in the regimen table. Then set the RegID to a one to many relationship with the CycleID in the cycle table.

Do I need to set up another table as a go between so that each MedRecordNum gets paired with the appropriate RegID's and CycleID's? Or will setting the relationships do that for me.

Does this make sense?

 

[traingamer]

You need to set the MedRecordNum in the main table to relate to a MedRecordNum (foreign key) in the regimen table (not with the RegId). In the same manner you need to relate the RegId in the regimen table with a RegID (foreign key) in the cycle table.

e.g.

main table
MedRecordNum (key)
other field 1
...

regimen table
RegId (key)
MedRecordNum (foreign key)
other fields


cycle table
CycleId (key)
RegID (foreign key0
other fields


You do not have to name the key-foreign keys with the same name, but it can help you see the relationship clearly.



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[BNPMike]

I'm not answering your question here but I would remind you you do not need to 'set' relationships. In relational databases the relationships are created by the fields you choose and the values you put in them. You can therefore play to your hearts content exploring relationships just with SQL. One of the original objectives of the relational model was to make sure 'information' such as relationships was not hidden within the system but exposed in the data.