0

I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned are order_details and order. Basically what is going to happen is user selects foods from restaurant menus and will place order, User details are stored in the user table (which is not shown in the image).

This is my first attempt, I was never good at Database. So please take it easy if you fill that the question is way too low for this site.

And my questions are:

  • Are the tables good enough?
  • Do I need to add any more fields?

  • And one more question I need to store multiple addresses of users(user can have multiple shipping address) how can I handle this?

    https://i.sstatic.net/HCYBi.jpg

Note The item_price table in the image is not i use. Please ignore that table while answering. Thanks

Improve this question
7
  • Looks pretty good. menu_category_item is a little unclear, it implies something about categorization; I'd consider "menu_item". If restaurant_facilities has a one-to-one relationship with restaurants, I'd leave them in a single wide table, but I'm not a fan of horizontal partitioning. You'll probably want price and tax on order_details; some places charge different taxes for food vs alcohol, for example.
    – Jon of All Trades
    Commented Jul 11, 2013 at 14:39
  • @JonofAllTrades menu_category_item contains menuId, ItemID, RestaurantID, categoryID, Price as same same item can belong to different restaurants with different prices. Yes restaurant_facilities has one-to-one relationship with restaurants. And should I move Tax, Discount, Total, Additional Charges to order_details? or Only Tax and Total?
    – user92961
    Commented Jul 11, 2013 at 15:08
  • Yes, I can see that. order_details should definitely include unit_price, total_price, and tax; I'd leave them in orders too, it's denormalization, but a very common and reasonable exception.
    – Jon of All Trades
    Commented Jul 11, 2013 at 16:20
  • discount is a tough call; do you expect most discounts to be off of a specific item, or off of a whole ticket? For the greatest flexibility, I'd make discounts a type of menu item. A person's order may include 1) Steak, $20 and 2) Salad, $5 and 3) Free Salad with any meal on Tuesdays, -$5. The nice thing about this is that it easily handles cases like this where a discount depends on a collection rather than a single line item.
    – Jon of All Trades
    Commented Jul 11, 2013 at 16:22
  • @JonofAllTrades Basically discount will be off of a whole ticket, but I can see your point and its seems good to me also for future, but can you please tell me how can I implement this in current design?should I move discount to menu_category_item
    – user92961
    Commented Jul 11, 2013 at 16:27

2 Answers 2

Reset to default
2

And again ;) I recommend that too often these days.

The Data Model Resource Book, Volume 1.

Order management, address management - solved, problems explained, well documented. Complex but you can simplify - just reading it will really help you understand the problems at hand.

Improve this answer
0

I think there are many problems in your design , but the most important ones according to the order functionality your are more worried about:

why item and item_price are separate tables? and why the order_details is not linked to item? you should provide a table for orders which is holding general data about each order, and a order_details which describe order items in multiple rows. each record of this table should be linked to an item and a record of order . in fact the relation between order and item is many to many and the order_details is the relating table.

to provide multiple addresses for each user, define a user_address table which stores all users' addresses, and link orders to user_addresses instead of users.

Improve this answer
3
  • Item_price table is removed, and its not in use, sorry it was included in image, order_details is linked with item through menu_category_item, read my above comments if you need better understanding
    – user92961
    Commented Jul 12, 2013 at 5:51
  • @user92961 : So you should provide more details about the project definition and update the attached image if you want new visitors to help you better. according to your comments , I don't see any special problem in your design, and for the case of discounts, it deeply depends on your project definition and requirements, as I told at first
    – Aliweb
    Commented Jul 12, 2013 at 11:02
  • @user92961 thanks for your help..will try to update the image..as soon as i get the required rep to post images I will edit my question with the image
    – user92961
    Commented Jul 12, 2013 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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