Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Multiple types of orders

    Hello,
    I'm not sure how best to deal with the following.

    Suppose I have an online movie rental store. Customers come to my website and rent DVDs. After renting a movie, they have the option of purchasing it.

    Thus, there are two types of orders: rent and purchase orders.

    Rent orders requires a shipping address; however, purchase orders do not.

    #1) One solution is to create two separate sets of tables:
    {rentorder, rentorder2movie}
    {purchaseorder, purchaseorder2movie}

    #2) Another solution is to dump everything into one set of tables.
    {order, order2movie}
    Where order has the field "ordertype". When the shipping address fields are not needed, then they are set to NULL.

    #3) Another solution is to create a third table:
    {order}
    {rentorder, rentorder2movie}
    {purchaseorder, purchaseorder2movie}

    Where order has two fields: orderID and ordertype

    I think #2 is a bad solution because it does not scale with the addition of multiple orders. You will end up with a table with lots of fields. Also, you need to somehow keep track of which fields need to be set for which order type, which can become complicated and makes verifying database integrity more tedious and prone to error.

    I'm not sure if there are any tangible benefits of doing #3 over #1. And I'm not sure if there exists other solutions which have eluded me. I would really appreciate advice on how to handle this sort of problem. Thanks in advance.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Since they have to rent the movie before they purchase it why not just have one order table and add a flag in the order2movie table that indicates which of the movies they (eventually) purchased?

    Order:
    287 - Jim Smith, 100 North Main
    Movie:
    888 - Bad Movie1
    889 - Bad Movie2
    Order2Movie:
    Order 287, Movie 888, Purchase N
    Order 287, Movie 889, Purchase Y

  3. #3
    Join Date
    Aug 2009
    Posts
    5
    Hello MarkATrombley, thank you.

    I would also need to store some billing information (name, address etc.) for the purchase. Also, I can't assume that the billing information used to make the purchase will be the same as that used for the rental.

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Then your best option is #2. If you use varchar columns the storage space taken up by nulls will be insignificant, and having less tables to deal with will make the programming easier.

Posting Permissions

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