I am quite a new comer to database design and also have some questions regarding guidelines and would love to understand. I'm creating a database schema, I've got a wise decision from the needs and today its dependent on setting it up into black and whitened.

Within this pseudo-database-layout, I've got a table of clients, table of orders and table of items.

TBL_Items:
ID
Description
Particulars

TBL_CUSTOMER:
ID
Title
Address

TBL_ORDER:
ID
TBL_CUSTOMER.ID
prod1
prod2
prod3
etc

Each 'order' only has one customer, but could have a variety of 'products'.

The issue is, during my situation, the items for any given order could be anywhere (100s for any single order) in addition, each product to have an order needs not only a 'quantity' but could have values that span pages of text for any specific product for any specific order. My real question is, how do i store that information?

Presuming I can not store a flexible length array as single area value, another choice is to possess a string that's delimited in some way and split by code within the application. A purchase might have say 100 items, each product getting either merely a small int, or 5000 figures reely text (or anything among), unique simply to that order.

In addition, each order should have it's own audit trail as numerous things may happen to it throughout it's lifetime. An audit trail would retain the usual information - user, time/date, action and may be any length. Would I store an audit trail for any specific order in it's own table (because they can be quite extended) produced because the order is produced?

What are the places where I possibly could find out more about approaches for database design?

The most typical way is always to keep order products in another table.

TBL_ORDER:
ID
TBL_CUSTOMER.ID

TBL_ORDER_ITEM:
ID
TBL_ORDER.ID
TBL_PRODUCTS.ID
Quantity
UniqueDetails

Exactly the same can use for your Order audit trail. It's really a new table for example

TBL_ORDER_AUDIT:
ID
TBL_ORDER.ID
AuditDetails

To begin with, Google Third Normal Form. Generally, your tables ought to be 3NF, but you will find cases when this isn't the situation due to performance or simplicity of use, and just experiance can definitely train you that.

That which you have isn't stabilized. You'll need a "Join table" to implement the numerous to a lot of 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_Particulars

The fundamental conventional reputation for the ID column within the Orders table (plural, because ORDER is really a keyword in SQL) is "Order Number", using the exact spelling different (OrderNum, OrderNumber, Order_Num, OrderNo, ...).

The TBL_ prefix is unnecessary it's doubly unnecessary because it does not always mean table, for example within the TBL_CUSTOMER.ID column title utilized in the TBL_ORDER table. Also, it's a bad idea, generally, to test utilizing a "." in the center of a column title you would need to always treat that title like a delimited identifier, attaching it either in double quotes (standard SQL and many DBMS) or square brackets (MS SQL Server unsure about Sybase).

Joe Celko offers quite a bit to say of such things as column naming. I do not accept all he states, but it's readily searchable. See also Fabian Pascal 'Practical Issues in Database Management'.

Another solutions have recommended that you'll require an 'Order Items' table - they are right you need to do. The solutions also have spoken about storing the amount inside. Remember that you will need not only the amount. For instance, you will need the cost prevailing during the time of an order. In lots of systems, there is also to cope with discount rates, taxes, along with other particulars. And when it's a complex item (as an plane), there might be just one 'item' around the order, but you will see a massive quantity of subordinate particulars to become recorded.