Relational Database Example - Order System
This presentation illustrates a very simple order system.
One thing to consider is carrying quoted price on the ordline table so that you know the price at the time of the order. That is a business decision - whether to take the price off the inventory at the time of billing etc or to carry it with the order as the price at the time of the order.
This is the inventory table. The ItemNo is the prime key and all of the entries are fields that are in a one to one relation with the prime key.
All of the fields on this table are in a one to one relationship with the customer id.
I have made the order number (ordno) the primary key of the orderz file. This works fine as long as we do not reuse the order number. If your company reuses order number that something else needs to be used as a second component of the primary key to make it unique.
Remember a primary key has to uniquely identify a record on the table.
If in the same order, a person could have two line items for the same item, the primary key that I devised would not work. I am assuming that the customer can only have one line item order for each item in this system.
There are six tables in this system:
inven with a primary key of Itemno
invcust with a primary key of Custid
orderz with a primary key of Ordno
ordline with a primary key of Ordno + Itemno
salesrep with a primary key of Slsrep
department with a primary key of Dept
Through tools you can select relationships to establish the relationships above.
I chose to enforce referential integrity on all of the relationships.
Note the one to many aspects of the relationships.
This query is taking data from the Inven and Department tables based on a match between Dept. Basically, we are showing information from the Inven table and then grabbing into the Dept table based on Dept to get the department name and manager name.
These tables are linked on Custid. The Orderz table is being shown with a link into the Invcust table to get the customer name.
This shows information about the order from the order header table and the order line item table based on the link of ordno.
This slide shows the link of three tables: Orderz and Ordline are linked based on Ordno and then Inven and Ordline are linked based on Itemno.
This is using the order and order line as the main source of data with infomration be taken from the customer and inventory as well.