In a previous article, we discussed the Patrick's Widgets scenario and began to lay out the requirements for an inventory and order tracking database that the company plans to implement in Microsoft Access. In this article, we begin to structure the data into tables that can be used in any relational database.
The first type of information that well want to keep track of in our database is our inventory status. The information we need here is relatively straightforward a list of all of the items that we sell including item IDs (which are unique), a description of each item and the current inventory on hand. The inventory table we might use to track this information is shown below:
Similarly, we need a table to help us keep track of customer contact information. Once again, well use a unique identifier called Customer ID to define each record. The data included in this table is shown below:
Now that we have the basics, its time to begin tying data together. Each time a customer places an order, well need to track that information in the database. The basic information for each order includes a unique Order ID, the Customer ID of the entity placing the order, the date/time the order was placed, the data/time the order was shipped and the tracking number. You may recall that one of the requirements from the previous installment of this series stated that we need to be able to print a report of all unshipped orders. Well track these by using a NULL value in the DateTimeShipped field to identify those orders that have not yet shipped.
The Orders table is shown below:
You probably noticed that the Orders table was missing one critical piece of data the items and quantities that the customer ordered! This information was intentionally omitted from the Orders table to maintain database normalization. Instead, well use a separate OrderedItems table to track this data. It ties together order records with inventory records using the OrderID and the ItemID. The table will be structured as follows:
Thats all there is to it! Weve designed a simple relational database structure. Join us for our next installment as we choose datatypes and actually build these tables in Microsoft Access!