views:

1807

answers:

6
+7  A: 

The most common way would be to store the order items in another table.

TBL_ORDER:
ID
TBL_CUSTOMER.ID

TBL_ORDER_ITEM:
ID
TBL_ORDER.ID
TBL_PRODUCTS.ID
Quantity
UniqueDetails

The same can apply to your Order audit trail. It can be a new table such as

TBL_ORDER_AUDIT:
ID
TBL_ORDER.ID
AuditDetails
metanaito
Don't forget to put "sale price" on TBL_ORDER_ITEM as it may be different from "unit price" on TBL_PRODUCT.
jmucchiello
+3  A: 
Charles Graham
A: 

Take a look at Agile Web Development with Rails, it's got an excellent section on ActiveRecord (an implementation of the same-named design pattern in Rails) and does a really good job of explaining these types of relationships, even if you never use Rails. Here's a good online tutorial as well.

Abdullah Jibaly
I don't get it, why is this being down-voted when I'm trying to answer Mrgreen's question about resources for learning techniques for db design? Have you guys read those chapters and disagree with the design techniques or is there another reason?
Abdullah Jibaly
I didn't down vote you, but I think the reason others did is the link you provided doesn't really have an answer, just a link to buy a book. It's kind of like the Experts Exchange model where you have to pay before you get anything. That is the opposite of what SO aims to be.
William Brendel
Great point, removed the link to the book.
Abdullah Jibaly
Voted down: explaining DB schema design through ActiveRecord is like explaining the fundamental laws of electricity by teaching how to plug in a power cable in a socket.
Andrew from NZSG
Andrew completely missed the point, if you review the chapters in the book I'm referring to it talks about all the different types of relationships and implementing them in the database, then it covers how ActiveRecord makes it easy programmatically.
Abdullah Jibaly
+2  A: 

The basic conventional name for the ID column in the Orders table (plural, because ORDER is a keyword in SQL) is "Order Number", with the exact spelling varying (OrderNum, OrderNumber, Order_Num, OrderNo, ...).

The TBL_ prefix is superfluous; it is doubly superfluous since it doesn't always mean table, as for example in the TBL_CUSTOMER.ID column name used in the TBL_ORDER table. Also, it is a bad idea, in general, to try using a "." in the middle of a column name; you would have to always treat that name as a delimited identifier, enclosing it in either double quotes (standard SQL and most DBMS) or square brackets (MS SQL Server; not sure about Sybase).

Joe Celko has a lot to say about things like column naming. I don't agree with all he says, but it is readily searchable. See also Fabian Pascal 'Practical Issues in Database Management'.

The other answers have suggested that you need an 'Order Items' table - they're right; you do. The answers have also talked about storing the quantity in there. Don't forget that you'll need more than just the quantity. For example, you'll need the price prevailing at the time of the order. In many systems, you might also need to deal with discounts, taxes, and other details. And if it is a complex item (like an airplane), there may be only one 'item' on the order, but there will be an enormous number of subordinate details to be recorded.

Jonathan Leffler
+1  A: 

Learn Entity-Relationship (ER) modeling for database requirements analysis.

Learn relational database design and some relational data modeling for the overall logical design of tables. Data normalization is an important part of this piece, but by no means all there is to learn. Relational database design is pretty much DBMS independent within the main stream DBMS products.

Learn physical database design. Learn index design as the first stage of designing for performance. Some index design is DBMS independent, but physical design becomes increasingly dependent on special features of your DBMS as you get more detailed. This can require a book that's specifically tailored to the DBMS you intend to use.

You don't have to do all the above learning before you ever design and build your first database. But what you don't know WILL hurt you. Like any other skill, the more you do it, the better you'll get. And learning what other people already know is a lot cheaper than learning by trial and error.

Walter Mitty
A: 

While not a reference on how to design database schemas, I often use the schema library at DatabaseAnswers.org. It is a good jumping off location if you want to have something that is already roughed in. They aren't perfect and will most likely need to be modified to fit your needs, but there are more than 500 of them in there.

joseph.ferris