Mysql – Auto-increment key and Foreign Key as composite for relational database

auto-incrementdatabase-designforeign keyMySQL

I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the internet and I have become increasingly confused as to how I will do what I want to do. Below is what I am trying to accomplish.

I have a need for a MySQL database that will hold patient information. Patient information will be name, id (unique), and so on as well as all of the tests and samples for each test.

  • Each patient can have multiple tests
  • Each test can have multiple samples
  • If I delete a patient then all test and samples are deleted
  • If I delete a test then all samples will be deleted
  • If I delete all samples of one test OR all tests of one patient then the test OR patient should not be deleted.

So far I understand a relational database is what I need. I also understand that I can use foreign keys to link the patient id's for each table as well as the test id for each table. I have also come to the conclusion that I would like data integrity so that I don't have to manually manage deleting and updating the rows. This would also not allow for orphaned rows. I believe this can be done with a composite key but this is where I start getting confused on how to set it up.

I would expect it to work like this (with each line being an insert statement):

Patient Table:

|-------------------|
|Patient ID  |Name  |
|-------------------|
|12345       |ANG   |
|54321       |JUE   |
|-------------------|

Tests Table:

|----------------------|
|Test ID  |Patient ID  |
|----------------------|
|1        |12345       |
|2        |12345       |
|3        |12345       |
|1        |54321       |
|2        |54321       |
|4        |12345       |
|----------------------|

Sample Table:

|----------------------------------|
|Sample ID  |Test ID  |Patient ID  |
|----------------------------------|
|1          |1        |12345       |
|2          |1        |12345       |
|3          |1        |12345       |
|1          |2        |12345       |
|1          |1        |54321       |
|1          |2        |54321       |
|2          |2        |54321       |
|2          |2        |12345       |
|----------------------------------|

Can this be done easily? I also know that this can be accomplished with one or two triggers but I understood that to not handle deletions and the like. Is there another way to do this that is easier to implement? Also, data being written and read from this database is handled by my LabVIEW program.

Best Answer

You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT 
   +
   |
   ^
 TEST 
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient. If you delete a patient, their tests must be cascade deleted. If you delete a test its samples must be cascade deleted. Therefore you don't need patient_id in the TEST table. All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...
Related Question