Database Design question

[jbeaulac]

I am having a design problem I can't seem to get around and was hoping
someone could guide me in the right direction. I have a supplier that has
several different styles and colours of product. Each box they send has an
unique box number that contains one particular style and colour. So, for
example, Colour and Style 1 has box number 1, 2, 3, 4, and 5 associated with
it, while Colour and Style 2 has 6, 7, 8, 9, and 10, etc.

I have set up a table that holds colour and style information, and a table
that holds the box information. There is a lookup to the colour and style
from the box table. I have no problem receiving the boxes into the database
and associating them with the right colour and style. That works fine, but
what I can't seem to figure out is shipping the box numbers out on orders. I
can't seem to associate the box numbers with both a receiving and shipping
record (I would like to keep track of both). The only solution I have been
able to come up with is to combine the box and colour/style table into one
table, but that means entering the colour/style over and over again on each
box number, which seems to defeat the purpose of a relational database.

I'm not sure I've given enough information, but any help or suggestions
would be appreciated.

 

[mscertified]

Correct me if I'm wrong but don't you ship boxes and not box numbers?
What you have described classifies boxes into 'types of box' but you still
need a table to contain the actual box of a given type.

-Dorian

 

[jbeaulac]

Correct me if I'm wrong but don't you ship boxes and not box numbers?
What you have described classifies boxes into 'types of box' but you still
need a table to contain the actual box of a given type.

-Dorian

The box numbers and the boxes are one in the same, each box having an unique
box number.

If I understand what you are getting at, I should have another table that
holds the box quantity number (in this case 1 for each box number) and work
on the data from there?