Database schema design?

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

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.

Don't forget to put "sale price" on TBL_ORDER_ITEM as it may be different from "unit price" on TBL_PRODUCT. – jmucchiello Dec 24 '08 at 4:51.

First of all, Google Third Normal Form. In most cases, your tables should be 3NF, but there are cases where this is not the case because of performance or ease of use, and only experiance can really teach you that. What you have is not normalized.

You need a "Join table" to implement the many to many relationship. TBL_ORDER: ID TBL_CUSTOMER. ID TBL_ORDER_PRODUCT_JOIN: ID TBL_ORDER.

ID TBL_Product. ID Quantity TBL_ORDER_AUDIT: ID TBL_ORDER. ID Audit_Details.

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 URL1 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.

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.

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.

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 Dec 24 '08 at 5:34 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 Dec 24 '08 at 5:44 Great point, removed the link to the book.

– Abdullah Jibaly Dec 24 '08 at 5:54 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 Jan 4 '09 at 5:09 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 Jan 4 '09 at 6:22.

Your design is better than Polymorphic Associations, which is an alternative that many people implement. It's kind of the reverse of what you've designed.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.