What is wrong with this SQL schema?

views:

answers:

+4 Q:

What is wrong with this SQL schema?

I'm designing my first database for MySQL and the idea here is that we have orders that can contain multiple different items. So i decided to store order with all the relevant info in one table, items in another, and then create a third table where each ordered item is stored. Of course every time i will need to list an order i first will have to find every OrderedItemsID for which OrderID is the same as the OrderID i need and than match that to items.

Now since this is my first DB design i'm not arrogant enough to think that it's a good solution, so i was wondering if anyone knows a good solution for this sort of problem? Thanks

image is at http://img211.imageshack.us/img211/5575/stackoverflowq.png

+3 A:

This solution is fine, although, in the orders table i would put in a total price, tax and shipping. This way you don't have to go to the OrderItems table to get the totals.

additionally, you will want to store everything from the item into the orderitem table. This will include the name and description. The reason is for accountability. If someone changes the name of an item in the table then that will essentially change it for everone's receipt. and you dont want to do that.

basically said, all information in the order tables needs to not be reliant on the actual items tables.

you can also take this further to add in the billing and shipping address so that if the user changes this in the future it wont change for this specific order.

Russ Bradberry 2010-02-01 06:05:40

+1: Excellent practical advice regarding accountability.

Max Shawabkeh 2010-02-01 06:13:47

I'm going to assume the immutable parts of his data, such as an archive of receipts is done through the not-shown "PriceSheet" table., I would argue that attaching that frozen-information, in random other non-consolidated places is bad practice. It creates update-abnormalities which should be avoided.

Evan Carroll 2010-02-01 06:33:59

@ Russ thank you.@Evan Carroll as for the Price Sheets they are sort of generated as a function of demand so they are not exactly frozen but rather fluid if anything. Then again - it's an interesting consideration. If you can throw a link or two that elaborates on the subject i would appreciate that.

lom 2010-02-01 07:30:45

Read 1NF-3NF + BCNF on wikipedia to understand the basics of DB normalization and update irregularities. In short, you don't want to ever store totals, aggregates, or *duplicate* data: but, if the data is the output of a invoice and all the prices are mutable then it must be store somewhere and it is often *safer*, and *easier* to do that. You also need a place to store a blanket of exceptions, like coupon codes, wholesale discounts and other pricing irregularities that aren't a function of Qty and Cost.

Evan Carroll 2010-02-01 16:06:06

No, the design is good enough. Except that an Item could have no related OrderedItem. (e.g. a brand new item put in the catalog may have not been ordered) The same could apply to Order too (e.g. an order has been made, but the customer hasn't decided the details of the order), but it's less likely.

shinkou 2010-02-01 06:10:11

What I meant is that an entry in the Items table may have no reference entry from the OrderedItems table. Whereas an entry in the Orders table may have no reference from the OrderedItems table, but it's less likely.

shinkou 2010-02-01 06:30:43

@Shinkou, Your situation is possible... But can you explain how to alter the DB for that...

The King 2010-02-01 06:37:19

@The King No, it cannot be enforced in the actual DB implementation. (i.e. nothing in SQL can do) It's something we do during the design phase. Please refer to the "zero through many" and "one through many" occurrence (also dubbed as crow feet) here: http://www.nearinfinity.com/blogs/lee_richardson/an_entity_relationship_diagram_example.html

shinkou 2010-02-01 07:22:17

@shinkou - i'll look into it. thank you.

lom 2010-02-01 07:26:30

@shinoku - I'm not sure I follow; there's nothing wrong with having items on catalogue that haven't yet been ordered. Or are you commenting on the fact that this has not actually been indicated on the diagram (using O markers for optional references)?

Craig Young 2010-02-01 08:06:36

@Craig Young I was referring to the diagram indeed, since lom's question is based on the diagram instead of a SQL statement.

shinkou 2010-02-01 08:56:26

+1 I think whoever downrated missed the point that strictly speaking, the design *excluded* the possibility that `Items` could exist without associated `OrderedItems`. So in that sense it was a minor error in the design. The diagram notation used does provide for optional-many (`>O---`) and optional-one (`---O1`) associations; the design would have been more accurate had it considered optionality on the one-to-many relationships shown.

Craig Young 2010-02-01 09:33:45

+1 A:

It looks just fine to me, by the way, you can also compare your tables to the sample database called as Northwind, which come with MS Office and easily available to download from internet as well. http://www.learn-sql-tutorial.com/Images/relationships.gif

http://msdn.microsoft.com/en-us/library/cc161164.aspx

http://www.microsoft.com/downloadS/details.aspx?FamilyID=c6661372-8dbe-422b-8676-c632d66c529c&displaylang=en

Ali 2010-02-01 06:18:59

thank you, i knew i wasn't the first person doing something like that.

lom 2010-02-01 07:27:36

ansaurus

tags:

views:

answers:

What is wrong with this SQL schema?

related questions