Ugh! Could use some table set up help!

[NeAlvey]

Hi all, I’m working on a temporary (so they say) database to keep track of case reports and I’m running into some issues. Very generically speaking, the logic works like this: The database will keep track of multiple studies. There are multiple patients in each study, and there is the possibility of many cases per ID. Where I’m running into problems is that an ID must be unique per study, however the same ID may be used across multiple studies – Both studies ABC and XYZ may have an ID 001.

Here are my tables (again, very simplified versions):

tblStudy (PK -> Study_ID)
Study_ID
Study_Name

tblPatient (PK -> Study_ID, Patient_ID)
Study_ID
Patient_ID
Initials

tblCase (PK -> Case_NO)
Case_NO
Patient_ID
Study_ID

Should I be referencing the Study_ID in tblCase or just the Patient_ID? Would I reference the Study_ID from tblStudy or tblPatient? If I have both and since they are 2 separate fields, it seems you could choose a study, and then an ID from the tblPatient that is not in the Study. I figure I can get around this in the form design, however it doesn’t seem like a good design to me. I usually try not to use Autonumbers as PK, however I’m totally against it. Thanks for the help!!

 

[neileg]

If you can have a duplicate Study_ID then it's not a candidate for a PK. PKs must be unique.

What is your aversion to an autonumber PK? I never use anything else. There's debate in these forums about meaningful and meaningless (ie autonumber) keys, and the meaningful key side has few supporters!

 

[NeAlvey]

If you can have a duplicate Study_ID then it's not a candidate for a PK. PKs must be unique.

There can't be duplicate Study_IDs. There are multiple studies in tblStudy, however they are all unique. There are then multiple Patient_IDs per Study_ID, and then multiple Case_Nos per Patient_IDs.

What is your aversion to an autonumber PK? I never use anything else. There's debate in these forums about meaningful and meaningless (ie autonumber) keys, and the meaningful key side has few supporters!

I guess it's just how I was taught in school. If I have fields that can be used as the key then I should use them. I'm not totally against them as I have 4 or 5 tables that have them (Case_No being one of them). It just seems weird to use them when I have unique studies and patients (to an extent).

Thanks for the reply! Still playing to see what I can come up with.

 

[The_Doc_Man]

While neileg and I differ on the desirability of using meaningful keys, your statement

Both studies ABC and XYZ may have an ID 001.

pretty much cements the issue. That ID number, by itself, cannot be a PK. There are methods of creating a compound PK (say, compound of Study code and ID number as the PK), but for speed & size issues you would often PREFER to avoid compound keys. (Not illegal to have so it can only be as strong as a preference, not a rule.)

The study code is probably a viable candidate key as PK for the study table. If this code is truly unique, it should be OK to use it.

I'm with neileg on autonumber for the table that lists the above ID numbers so that you can maintain proper relational integrity and uniqueness. Because your earlier comments suggest that otherwise, it WON'T have a viable PK candidate.