define table with many relations

[Susan Hoekstra]

I am defining a table and can't get it clear; See data: I have a boxtype as
primary key and product as foreign key. The problem however, is that one
boxtype might have several products in it. Or even when one boxtype has
product X in it, it contains 13 kilo/box, while when it has product Y in it,
it has 21 kilos.
I can split up tables, but it seems that I have a many - to many relation.
What is the solution?

boxtype ID kilo/box box/pallet product ID
33x27x11 4 240 mango
50x36x16 Dulce 10 84 YHD
50x40x16 Dulce 10 84 YHD
50x40x20 Dulce 16 60 watermelon
50x40x20 Marlin 16 66 watermelon
50x40x20 Negra 16 66 watermelon
50x40x20 White 16 60 watermelon
50x40x21 Cocorisa 16 60 watermelon
60x40 18 75 pineapple
60x40x16 Black 13 65 YHD
60x40x16 Dulce 13 65 YHD
60x40x16 Green 13 65 YHD
60x40x16 Royal Coast 13 50 Melon Blanco
60x40x23 Royal Coast 21 50 watermelon
60x40x23 Royal Coast 13 50 piel de sapo, YHD,Melon Blanco
bin 500 2 watermelon
El Barto 22,5 60 yuca
Thanks for answering

 

[John Nurick]

Hi Susan,

It sounds like a many-to-many relationship, along these lines:

tblBoxTypes
BoxTypeID (Primary key)
other fields
BoxesPerPallet
other fields

tblProducts
ProductID (primary key)
other fields

tblKilosPerBox
BoxTypeID (foreign key)
ProductID (foreign key)
Kilos
[Primary key consists of BoxTypeID AND ProductID]

However, if the kg per box is determined by the product and the volume
of the box, you might do better to store the internal dimensions or
volume of each box type in tblBoxTypes, and the density of each product
in tblProducts, and then calculate the kg per box on the fly whenever
it's needed. This would avoid having to have one entry in tblKilosPerBox
for every combination of product and box type.