1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

Building an Access Database From the Ground Up

Part 4: Building the Relational Model

By

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 we’ll 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:

Inventory
ItemID
Description
Quantity

Similarly, we need a table to help us keep track of customer contact information. Once again, we’ll use a unique identifier called Customer ID to define each record. The data included in this table is shown below:

Customers
CustomerID
Company
ContactName
Telephone
Address1
Address2
City
State
ZIP

Now that we have the basics, it’s time to begin tying data together. Each time a customer places an order, we’ll 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. We’ll 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:

Orders
OrderID
CustomerID
DateTimePlaced
DateTimeShipped
TrackingCode

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, we’ll 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:

OrderedItems
OrderID
ItemID
Quantity

That’s all there is to it! We’ve designed a simple relational database structure. Join us for our next installment as we choose datatypes and actually build these tables in Microsoft Access!
  1. About.com
  2. Technology
  3. Databases
  4. Microsoft Access
  5. Tutorials
  6. Building an Access Database
  7. Building the Relational Model

©2014 About.com. All rights reserved.