I want to make an order system in which an Item can optionally ordered with an Option or more. (think of Item as Pizza and Options as toppings)

In other words,
1- An Item can be ordered alone.
2- An Item can be order with an Option or more.
3- An Option can't be ordered alone (without an Item).
4- The system should prevent adding the same item to the order assuming no options are chosen for both.
5- If two similar Items are being ordered but with different options, the system should allow that. For example:

Pepperoni Pizza with options: 1, 2, 3, 6
Pepperoni Pizza with options: 2, 3, 4

6- If two similar Items are being ordered with the same options, the system should not allow that.

Here are my tables:

Order(Order_Id, TotalPrice)
Order_Item(Order_Item_Id, Order_Id, Item_Id, Quantity)
Item(Item_Id, Name, Price)
Item_Option(Item_Id, Option_Id)
Option(Option_Id, Name, Price)

I wanted to have another table for keeping a record of the options ordered with thier Items for each order so I made:

Order_Option(Item_Id, Option_Id, Order_Item_Id)

If I make Order_Item_Id a primary key, the system will allow ordering the same Item with the same Options.

If I make Item_Id a primary key, the system will allow only one Option per Item.

If I make both Item_Id and Option_Id, they will be also a problem!

Imagine:
Item_Id Option_Id Order_Item_Id
1 1 33
1 2 33
1 3 33
1 1 34 <--Problem !

It is a problem because I might want to have Options (1, 4 , 6 , 7) with Order_Item_Id 34.

Any help will be greatly appreciated.

In this case, use a composite primary key with all three columns (Item_Id, Option_Id, Order_Item_Id). I would prefer the key in this order (Order_Item_id, Item_Id, Option_Id)

Ravi

Thanks Ravi,

but if I use a composite primary key with all three columns:

Order_Option(Order_Item_Id, Item_Id, Option_Id)

This will allow two or more similar Items to be ordered with the same options in the same order under different Order_Item_Id's (which I am trying to avoid). For Example:

Order_Item_Id Item_Id Option_Id
21 1 1
21 1 2
21 1 3

22 1 1
22 1 2
22 1 3

Notice that Order_Item_Id is an auto_increment PK in Order_Item table and also a FK in Order_Option table (As well as being part of the composite primary key in this case)

I think that should be handled by having a unique constraint on the columns (order_id, item_id) of the order_item table which I understood to be your business rule.

Whenever you create a surrogate key, like order_item_id, look for the possibility of creating a unique key based on a combination of other columns. While not always possible, a unique key generally makes sense in combination with a surrogate key.

Ravi

Sorry, I think I misunderstood the question.

In this particular case, your constraint can only be imposed programatically. You'd have to check at some point in time that two items, if part of the same order, must have different option sets.

One way to do this would be create option sets on the fly. That is, every time a set of options comes in, check if that set already exists. Now define your order_options table as (order_item_id, item_id, option_set_id). Add two more tables: option_set(option_set_id) and selected_options(option_set_id, option_id)

To summarize, here's how the suggested table structure would be:

Order(Order_Id, TotalPrice)
Order_Item(Order_Id, Item_Id, Quantity)
Item(Item_Id, Name, Price)
Item_Option(Item_Id, Option_Id)
Option_set(Option_set_id, ...)
Option(Option_Id, Name, Price)
Selected_Options(Option_set_id, option_id)
Order_Option(Order_id, Item_id, option_set_id)

All you'd have to do is to write a procedure that takes an array of options and creates the records in order_option table after checking if that set of options already exists. If the combination of options already exists, it creates a new row in order_option table using the option_set_id belonging to that set. If no such set exists, it creates a new row in the option_set table, creates the required rows in the selected_options table, and then using this new option_set_id, it creates a row in the order_option table. Any duplicates would be automatically weeded out by causing a duplicate primary key error in the order_option table.

Hope that helps.

Ravi

Dear rajiravi

First of all, thanks for the greet feedback

I guess the Order_Item_Id in the Order_Item table is very needed if I were to allow 2 similar items with different Options to be ordered.
Having both Order_Id and Item_Id as a composite key will only allow a particular item to be added once and only once to the order.

Order_Id, Item_Id, Quantity
1 2 2
1 2 <---- problem!

Ok, I see your point. Retaining the artificial key, order_item_id, and making the required changes will still work with the general idea of an option set, I think.

Ravi

I have no previous experience with procedures :/

I'm running MYSQL 5.0.20 as my database.

I will be very thankful if you could help me in writting the procedure.

Thanks

I am sorry I am not familiar with MySql. The logic for writing the trigger is there in the earlier posts. If you have any problems with the logic, I'll be glad to help.

Ravi