Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

closed as unclear what you're asking by StanleyJohns, Mat, Mark Storey-Smith, RolandoMySQLDBA, Max Vernon Sep 16 '13 at 2:48

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

No idea, given you haven't shown your table design for the customer table. If id isn't the PK in the boxes table and is non-unique, then you're ok. Your requirements aren't clear - state them in plain English – Phil Sep 15 '13 at 1:28
Thanks for you for pointing that @Phil , i edited my question. – C0ld Sep 15 '13 at 3:09
show create tables results pls – Praveen Prasannan Sep 15 '13 at 9:37
@Ursula has provided the answer that helped me. – C0ld Sep 23 '13 at 2:25
up vote 0 down vote accepted

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.

share|improve this answer
Mat, thanks for fomatting! Would have done it myself today - wasn't comfortable to do on touch device;) – Ursula Sep 15 '13 at 15:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.