Mysql – Multiple values for unique id?

database-designMySQL

I have two tables: customer and boxes in one-to-many relationship.
The structure of the tables is in addition :

Customer table :   id#| name | date_purchased | address |
                    1    John     2012-9-10       N/A

Boxes table:   id#| type | quantity |
                1    big      4 

The id of the boxes table is foreign key to the customer table.

What if John has purchased more than 4 big boxes ? For example: he has ordered 2small boxes as well. How to add this to the database design schema?

Best Answer

I would create three tables instead:

Customer table:

  • Customer_id, name, address, etc.
  • PK: customer_id

Boxes table:

  • Box_id, size, description, etc.
  • PK: box_id

Orders table:

  • Customer_id, Box_id, Quantity, Timestamp.
  • PK: all four fields.
  • Foreign keys box_id and customer_id to the respective tables.

You will have one entry for each customer, each box type, and as many boxes of different sizes ordered as you wish.