CODEWITHSUNDEEP
database
Jack_of_All_Trades
Jack_of_All_Trades

Reputation: 11468

database design, Is this one to one relationship?

Situation Statement:

Our lab receives the samples from different sources. Upon receiving, we assign them the lab id number which is unique for each sample. We perform variety of tests on each samples, lets says, test1, test2, test3.... and the results from this tests are often in multiple values.

So far what I think is:

Samples Tables:

Sources Table:

Obviously, there will be one to many relationship between Sources to Samples Table because one source can have multiple samples that are sent to our lab.

Now, my problem starts with creating the table that stores the data from each test. I am thinking that I should store the results of each tests in separate table. Each sample could be subjected to one or multiple tests.

For example, test1 table can be like this:

Test1 Table:

Similarly, other test tables:

Test2 Table:

Test3 Table:

and so on...

My dilemma( things I am unclear about):

It is clear to me that Lab ID can serve as unique key (PK) for each test tables. Now, if I create one to one relationship between Samples table and Tests table, it requires that each LabID should be repeated in all the Tests Table (Correct me if I am wrong!). However, the sample might not be subjected to that particular test.

Why I think one-to-one is not the right choice?

If the record is deleted from the Test1 table, there may still be other tests and data in other table so deleting the record from Samples Table (which I think will happen if I go with one-to-one relation) is not correct.

My question is:

How can I specify correct relationship in the situation like this? I am completely new to database design and my experience is 3-days of random tit-bits of reading here and there.

Upvotes: 0

Views: 59

Answers (1)

geoandri
geoandri

Reputation: 2428

I 'd create tests and values tables as shown below

Tests

  • id
  • test_name
  • other data

Values

  • id
  • Value_label (carbon, hydrogen, nitrogen)
  • other data

Also I'd create a measurements (or anything you want) table with columns

Measurements

  • id
  • test_id (fk references id to tests table)
  • value_id ((fk references id to values table))

That table represents your tests structure.For example if test1 has 3 parameters to be measured three corresponding rows will be present in the table, one for each parameter.

Then you can create a Results table where you can store the tests results data. The proposed structure could be

Results

  • id
  • sample_id (fk references LabID on samples table)
  • measurement_id (fk references id on measurements table)
  • value (the value of the result)

Hope it' s not too complicated

Upvotes: 1

Related Questions