The database for the PC store will require many different tables. The store has an inventory of computers. Therefore, the first obvious table to be added to the database is computer_inventory. Each computer comes from a manufacturer (e.g., Dell, Compaq, ASUS, Apple). There should be a manufacturer table that can store each of the manufacturers along with the contact information for that manufacturer. The computer inventory table will store how many of each type of computer is on hand.
There should obviously be a sales table. Since the sales occur at a POS system, there will not necessarily be detailed information about customers. However, this table will reference the item that was purchased, the price of the purchase, the sales tax, the payment type, and payment amount.
Finally, there needs to be a table to store repurchasing. There needs to be an event that triggers the restocking of items in the computer inventory table. When this event occurs, it should be recorded in the database along with the date and status. For instance, if the store is running low on stock of Apple Airbook, something should be triggered that stores a row in a restock table with the item’s inventory number and restock status. The restock status is a simple flag to show if the item has been reordered. It could include: not reordered, reordered, backordered, and no longer available. Of course, just like the sales table, this table will contain dates for all of the important events (i.e., when the restock is triggered and when the order is placed).
Based on the data given about sales, the sales table will need to be further normalized. A sale can consist of a purchase order. A purchase order may contain more than one order item. In this case, a join table will need to be created. A join table is used when there is a need to join the primary keys of two tables to facilitate a potential many-to-many relationship. In this particular instance, it could be argued that an item in the computer inventory may exist in many purchase orders. Likewise, a purchase order may contain many items from the computer inventory table. We cannot store multiple computer inventory IDs in the sales table because no single column within a table can hold multiple values. This would violate first normal form. Therefore, an additional table is added. The sales table still holds general information about the sale, such as a primary key, the total sale amount, the payment type, and the total tax. Then there will be a sale_item or purchase_item table that will hold the primary key of the specific sale along with the primary key of the computer inventory item sold. The combination of the sale_item primary key and the purchase_item primary key will be the complex primary key on this join table.
There are various foreign keys throughout the tables. The sale_items table consists of only foreign keys, which also make up the complex primary key. The computer inventory ID is a foreign key to the computer inventory table. The sale ID is a foreign key to the sales table. The restock table obviously has a foreign key to the computer inventory table. These foreign keys tie the tables together.
It is also important that various indexes are added to the tables to support the more comment reports. For instances, an index could be added to the computer inventory ID within the sales table. This way, when the sales table is queried for all sales on a particular model, the query will be more efficient. Of course, it must be taken into consideration that indexes can decrease the efficiency of inserts and updates. Therefore, they should be chosen and implemented wisely.
The database will have three packages: The Sales Package, The Inventory Package, and The Reports Package. These packages will hold various procedures that perform the operations that are needed for the system. For instance, the sales package will have procedures to perform a sale. It may also consider procedures to reverse a sale, refund a sale, or update a sale. The reports package will obviously hold all of the procedure necessary to return reports that are important to management.
As previously mentioned, there needs to be something to trigger the restocking of items. The sales package procedures will have an impact on the quantity on hand value within the computer inventory table. The quantity will decrease as sales are processed. The inventory table could hold a restock level for each item. I would recommend that a trigger is added to the database that runs after update on the computer inventory table. The trigger will review the current quantity versus the restock level. If the current quantity is now equal to the restock level, a row will be inserted into the restock table. In this manner, most of the functionality of the database is implemented and complete at the DBMS level.