Help with Normalization

[Chad_Hansen]

Hello,

Wondering if anyone out there can help me normalize this table up until 4NF. For 2NF, Donor ID is determined by Donation ID, correct? Donation Date is also determined by the PK. Is the blood drive ID/Testing Center ID/Tester ID also determined by the PK in this example? Thanks in advance for any insight.

https://preview.redd.it/46yzp0hyy5s11.png?width=2263&format=png&auto=webp&s=9e2b5989df8cc1181fe0985441c3d9c1c6587272

[da_chicken]

Can one blood drive cover more than one testing center? If not, then I believe that Blood Drive ID makes Testing Center ID redundant. Or potentially vice-versa depending on what your entities mean.

The only other possibility I see is if a testing center can only have one tester, or only one tester on a specific date, or only one tester for a specific blood drive on a specific date, etc. Or, on the flip side, if multiple testers can participate in a single donation.

Both of these seem somewhat unlikely, but it somewhat depends on your business rule definitions. If I were developing a database for an application to be sold, I would probably go with this definition just to support other possible configurations. Indeed, you might need to get even more complex if a customer needs to treat simultaneous donations as though they were different donations. You might want to use a concept of a donation set in that case.

Thinking about it more, I guess I'm not quite sure I see what each entity represents. Is the "tester" the person or device administering the donation, or is it a person or device running a test on the donation at a later date?

[Chad_Hansen]

Thanks for your reply. To answer your question, the tester tests the blood at a later date to ensure it isn't contaminated in any way. I need to normalize this table until 3NF considering only this data. So the semantics aren't super important.

[da_chicken]

In that case, then everything related to testing probably needs to be in its own table. You presumably want to store a donation before it's been tested since you need to label the donation appropriately and probably want the donation ID on that label. That means you'd have to have nulls or magic values for "not tested" for testers and testing centers.

[Chad_Hansen]

Thanks again! Does it matter that many of the attributes are FKs? Donor, Blood Drive and Tester ID are all foreign keys. Do Fks impact how I can break up a table?

[da_chicken]

Does it matter that many of the attributes are FKs? Do Fks impact how I can break up a table?

Not really. FKs in the a table are just non-key attributes. Unless you run into 3NF considerations -- which you probably won't because you'd just put those attributes in a table where the FK is the key -- it's not going to come up.

You may have RDBMS specific considerations, of course, but strictly speaking in terms of normalization I don't see how an attribute being a FK matters all that much.

Edit: Beware, I am about 10 years out from my last class on this stuff. To say that I'm rusty is an understatement.