Originally Posted by
ArviLaanemets
In Orders table you have the info about certain order, which you an present as a single row in Orders table. I.e. when the employee is making an order, it is one certain employee. And the order is made for one certain customer at one certain date, etc.
In OrderDetails table is info which you can't have as single row. P.e. when you send to customer the order about 10 different items/services, and every one of them has different quantity and unit price, then you have accordig 10 rows into OrderDetails table with all this info a row for every item/service, and link those rows with a row in Orders table through OrderID.
Btw, I think it is better to have an additional field OrderDetailID in OrderDetails table as primary key. It is possible a situation, where you need 2 rows for same product for an order, p.e. in case you are giving discount only for quantities above certain level. With current 2-field primary key it is not possible, as you get primary key violation.
To control effectively the data entry, you have to use user interface - forms. To register an order, the best setup is a single form based on Orders table, where you can register new orders and edit existing ones when there is a need for this.
On this form you have a continuous subform based on OrderDetails table. The subform is linked with main (Orders) form through OrderID field in both tables. When you select some order in main form, all order details rows for this order are displayed in subform. And when you add a new row into subform, it is automatically linked with main form (OrderID from main form active row is entered into subform field linked with OrderID in OrderDetails table).
When user is entering data manually into tables, then all entries must be done manually - and the risk to type in something wrong, or to forget something, is very high. So an advice almost everyone is giving here - never let user to dabble with your tables.