Database tables design

If I have, in my Java code, the following classes:

• Patient :contains instance of anther classes (mentioned bellow)

• PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age.

• PatientImageFinidgns: contains Date, comments, lesionSize, site, procedure.

• PatientLabFindings: contains Date, t1,t2,t3,t4.

• PatientTreatment: contains visitNumber, Date, some other data.

Id is the primary key for Patient and Date is very important key in both PatientImageFindings and PatientLabFindings. visitNumber is my primary key in PatientTreatment.

How could I design these classes in a database. Should I create a table Patient, which holds only foreign keys of the other classes (like ID, Date, VisitNumber) or should I put PatientId in the table Patient as a primary key and as a foreign key in the PatientBasicInformation table. If so, what would be the primary key in the PatientBasicInformation table?

I want appreciate a simple and clear answer as I'm a new to databases and I have read a lot of articles, but I still don't get the whole picture.

Put simply you should design your database so that all information is stored with a its natural level.

In your case, I would create a table Patients. Each row in that table should store all the pieces of information that are relevant to that patient and not dependent on anything else. The other tables then follow on from this. For instance, PatientVisits, the unique key would be the identifier of the visit; but you need to know what patient this visit relates to so you should also have a foreign key back into Patients. You schema then looks something like this:

-
Patients

• Id - primary key, auto-increment as there's no natural key

• Phone number, name, gender, date of birth.

Never store age, you have to re-calculate it each and every day. Store the date of birth instead so that you can easily work out the current age.

-
ImageFindings

• Id - primary key, auto-increment as there's no natural key

• patient id -- foreign key to the primary key of Patients

• created, comments, lesion size, site, procedure

I've renamed date to created as it's a reserved word and really quite confusing to use in a database. I doubt this should be the primary key as it's possible to insert two simultaneously

-
LabFindings

• Id - primary key, auto-increment as there's no natural key

• patient id -- foreign key to the primary key of Patients

• test - 1, 2, 3 etc -- foreign key to the primary key of Tests

• test result

• created

If a Image is dependent on a LabFinding then you should change the foreign key in ImageFindings to be the primary key of LabFindings. As you might have more than 1 test these should be split out into rows rather than columns as it might be possible for you to add more tests.

-
Treatment

• Id - primary key, auto-increment as there's no natural key

• patient id -- foreign key to the primary key of Patients

• more columns

• Tests

• Id - primary key, auto-increment as there's no natural key

• Description

• More data related to the test

You note that the primary key of every table is a surrogate key. This is because there is no natural key, i.e. one that should obviously be the primary key of the table. You ask what the primary key of PatientBasicInformation (Patients) should be; it can't be anything but a surrogate key as you can't rely that you will never have a patient with the same name as another, or the same name and date of birth etc. The naming conventions of people are inherently chaotic and so you can't rely that people will nicely fit into your database.

I would strongly suggest reading up about the Third Normal Form and trying to understand it.

StackExchange Database Administrators Q#20898, answer score: 5