4

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.

Improve this question
8
  • Should lab and image findings include the visitNumber? Commented Jul 14, 2012 at 11:00
  • @ Ben: i got the message thnx.. @ Nerd-Herd: actually i used to ask here, this case was new for me.. thnx for helping @ Mark: Logically yes, but in my application no. I don't know why is that? anyway i Patient may has more than one Imagefindings and labFindings, i differentiate between them by Date. so the date is an important key for both classes Commented Jul 14, 2012 at 11:41
  • I don't think you are starting from the correct point. You should start from a business case then you either design an OO representation or an ERD. Business case is most important. However, you already made your mind on specific classes that I don't think they are good enough for the subject. Commented Jul 14, 2012 at 21:46
  • @Ben, did you ask for this question to be moved form SO here? Commented Jul 15, 2012 at 9:31
  • 1
    @DamirSudarevic, no I didn't vote for migration but it doesn't make that much difference. I don't believe that if something is on-topic on a site it should be migrated, even if would be equally on topic on another site. Commented Jul 15, 2012 at 9:35

3 Answers 3

Reset to default
5

This is focused on your question and classes as specified. I am not sure that LabFindings is properly normalized, those t1,t2,t3.. sound suspicious, but we would need more detailed explanation for that.

  • Something like this

enter image description here

  • If for some reason you have trouble with compound keys, you could also use

enter image description here

Improve this answer
2
  • Great thnx for you @Damir Sudarevic.. you really helped me Commented Jul 14, 2012 at 11:08
  • What tool did you use to draw the diagrams? Commented Apr 29, 2016 at 16:07
5

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:

  1. 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.

  2. 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

  3. 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.

  4. Treatment

    • Id - primary key, auto-increment as there's no natural key
    • patient id -- foreign key to the primary key of Patients
    • more columns

  5. 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.

Improve this answer
1
  • thnx a lot Ben your answer really helped me. Commented Jul 14, 2012 at 11:44
1

Any information that has a 1:1 relatatuonship to the patient can go into the patient table, ie stuff like name, birthdate, blood type, gender... all of these things the patient can have only 1 of so you dont need to worry about supporting multiple records in the same table for the same patient because all details can be stored in a single row

For 1:Many relationships, this is where you include the patient id as a foreign key. So for example maybe you have a visit table that holds data like room, date&time, facility, doctor (if only 1 doctor will ever be assigned to a particular visit). A patient can have many visits, but each visit only has 1 patient,thus the 1:Many relationship. Since visits must be for a valid patient, this is where the foreign key checks referential integrity because you wouldnt want a visit record with a nonexistant patient id

Testing would be a good example of a many:many relationship as it could hold many (types) of tests and each patient could have multiple tests. Here you should but specifics about the test itself in a test table (cost, result eta, test type) and then make a patienttests table to be the bridge between patients and tests. Here for patient id and test id would be foreign keys, but usually also participate in a multi-column primary key. If patients could only have each test type once, then a patient id+test id orimary key would be unique and thus serve as a suitable primary key, but since patients could be retested a 3rd column like date or visit id would be necessary to make that patient-test record unique.

Read up on database normalization. Its going to take some practice and it might seem like you are storing redundant data at first but once you understand the normal forms you will understand how to properly define the relationships between your tables and when to put data in the same table vs create a linked table with foreign keys

Improve this answer