Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    4

    An Item with Options

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Use composite Primary Key

    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

  3. #3
    Join Date
    May 2006
    Posts
    4
    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)

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Have Unique Key on Order_Item table

    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

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  6. #6
    Join Date
    May 2006
    Posts
    4

    Order_Item_Id is needed

    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!

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  8. #8
    Join Date
    May 2006
    Posts
    4
    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

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •