Database Table Design

[SamLis]

Hello,

I want to validate if my structure is correct :

I need a database of Samples (liquids) that come in different volumes.
They are stored in boxes, however different volumes of the same sample can be in the same box.
Also one Sample,in one volume can be spread over more boxes.
One box can contain different volumes of same sample, but also other samples with different volumes.

I included a pic of what I think is ok?

thanks,
Sam

 

[CJ_London]

Looks OK to me in principle, but not sure why you need so many tables

Why not just have:

tblSample
SampleID
SampleName

tblBox
BoxID
BoxName

tblBoxContents
BoxID
SampleID
SampleVolume

 

[SamLis]

Hi,

1)SampleVolume are fixed volumes ( like bottles Cola; 33 cl, 1 L, 2 L) that's why I put them in a separate table. one sample can come in different volumes, but different samples can have same volume

2)If I understand your tblBoxContents :

One box can have different samples, but does it cover that one sample could be found in different boxes?

That's why I thought to need 2 many-to-many relationships?

Sam

 

[CJ_London]

SampleVolume are fixed volumes ( like bottles Cola; 33 cl, 1 L, 2 L)

Fair comment, but since it is a small list you could simply populate using a combobox with a value list of .33;1;2

One box can have different samples, but does it cover that one sample could be found in different boxes?

yes

If a box contains say 12 samples you would have twelve rows, one for each sample.

To cut down on the number of rows you could have

tblBoxContents
BoxID
SampleID
SampleVolume
Quantity

so say a box contains 3 bottles of sampleA .33cl and 2 bottles of sampleA 1 litre and 7 bottles of sampleB 2 litre, you would have 3 rows

 

[SamLis]

Hi,

Thanks, I will keep the the volume table, since I can't predict if people will divide to new volumes etc.

Then I just need to design a query based on tblBoxcontents?

So I can filter/search on :

What's the content of box A?

Answer : Sample A, 33 cl; Sample A; 1 L, Sample B

Where can I find Sample A? no volume specified- all volumes
answer : Box A, Box C, Box D, ...

Sam