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.
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.
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:
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.
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.