The following sections show how the database table relationships were designed. The object names are provided so you can easily examine them in the Northwind 2.0 Starter Edition database.
To open the Relationship Diagram showing the six tables and the relationships between them, select Database Tools > Relationships.
This diagram shows all six tables. In the diagram, lines between tables identify relationships between them. The 1 and the infinity symbol (∞) at the ends of lines represent the one-side of a relationship (for example, one customer) and the many-side of a relationship. For example, one customer submits many orders. For more information, see Guide to table relationships.
The following principles apply to tables in Northwind 2.0 Starter Edition as well as tables in general.
Primary keys Uniquely identify each record in a table. All tables have a primary key. In the relationship diagram, Key symbols identify those primary keys. Primary key naming conventions are named for the table they are in, for example, "TableNameID".
Efficiency For better performance and more efficient storage, primary keys should be numeric. In addition, it’s more convenient to have Access automatically generate the new, unique value for each new record’s primary key. The AutoNumber data type has both characteristics. AutoNumbers are otherwise non-meaningful numbers and serve no other purpose. For more information, see Add an AutoNumber field as a primary key.
Foreign keys A table can also have one or more foreign keys, depending on whether it is related to other tables in the database. A foreign key contains values that correspond to values in the primary key of the related table.
Unique indexes Other fields in tables may also have their own unique indexes, for example, OrderStatus.StatusCode. It is illogical to have two Order Statuses in the OrderStatus table with the same code, even though StatusCode is itself not the primary key. A unique index tells Access to prevent duplicate values in that field.
Non-Unique indexes Tables may also have indexes to speed up searches and sorts on those fields, for example, Orders.OrderDate. Many orders can be placed on the same day, and you often want to search and sort on Order Dates. There is a non-unique index on that field to speed up searching and sorting.
Table and field names You can name things any way you want, but consistency is important. We recommend table and field names should be one or more words with no spaces between them, and no special characters such as a slash (/), pound sign (#), or percent (%). For Example, use OrderDate, but not Order Date; use OrderNumber or OrderNo, but not Order#.
CamelCase Capitalize words to highlight individual parts of the name, for example, OrderDate, but not Orderdate or orderDate.
Required value This principle brings up the importance of business rules for an application. Some situations require values or even specific values in some fields. For example, what good is an Order without knowing the Customer who placed it? That means CustomerID is a required field for the Orders table.
Calculated fields Access supports calculated fields in tables, for example, the Employees.FullName field. You may prefer to create calculated fields in a query rather than in a table.
Attachment fields Access supports attachment fields, for example Employees.Picture, which holds a picture of the employee. Attachments can store images, documents, emails, and other binary information. Attachments occupy a lot of space in the database. it’s more efficient to store attachments on a file server instead.
Multivalued fields As the name implies, multivalued fields store one or more values in a single field, for example, Employees.Title. We suggest that you use them sparingly, especially if you want to upsize your database. Most other database systems don’t have them, so that would require a lot of re-work.
For more information about data types, see Introduction to data types and field properties.
This section discusses the most important features of each table. To review the design of a table, select it in the Navigation Pane, right-click it, choose Design View, or select Database Tools > Relationships, and then right-click a table object. For more information, see Introduction to tables.
Important: Avoid the use of reserved words which may cause naming conflicts. For more information, see Learn about Access reserved words and symbols.
Employees table
This table stores information about Northwind’s employees.
|
Fields |
Description |
|
FirstName, LastName |
Both names are required and, in Northwind, together they must be a unique combination. In the table design, when you open the Indexes dialog box, you can see that FirstName + LastName have a unique index. Because FirstName and LastName are uniquely indexed, the Northwind table can’t store two employees with the same name. In other situations, you may use a different business rule. |
|
FullNameFNLN, FullNameLNFN |
Look at the expression property of calculated fields to see how Access combines values in calculated fields. To include a middle initial, add it to the existing expression with appropriate spacing between components. |
|
Phone fields |
The business rule for phones is that employee preference is more relevant than type of service. Therefore, primary and secondary phone numbers are used rather than cell, office, home, and so on. |
|
Salutation |
Salutation is a Short Text field. To illustrate the multivalued field feature in Access, it is a combo box with an editable list of pre-defined values. Short, static lists like this are often candidates for multivalued fields because they don’t change much, if ever. |
|
JobTitle |
JobTitle is another required field. |
Customers table
This table stores information about Northwind’s customers.
|
Fields |
Description |
|
CustomerName |
Northwind’s customers are businesses, and a customer name is required. Unlike employee names, though, it’s not uniquely indexed, allowing two or more customers to have the same name. |
|
PrimaryContactFirstName, PrimaryContactLastName, PrimaryContactJobTitle |
The primary contact’s first and last names and job title are not required because customers may not have one individual as their primary contact. Contacts may not give their job title for an order. |
|
BusinessPhone |
Northwind requires only one phone number for each customer, although this eliminates the ability to capture multiple phone numbers for customers or for contacts from customers. In real-world situations, more complex business rules normally apply to contact information. |
|
Address, City State, ZIP |
Northwind needs an address to ship orders to customers. There is only one generic address for a customer. In real-world situations, customers often have separate billing, shipping or other addresses. A different business rule for your organization would require additional fields. |
|
Notes |
The Notes field is a Long Text datatype, which stores up to 1 GB of text. That allows you to enter detailed comments about customers for use in subsequent ordering situations. |
Orders table
This table stores information about Northwind’s orders.
|
Fields |
Description |
|
OrderDate, ShippedDate, PaidDate |
Orders require three dates. They are all Date/Time data type, but with two formats. OrderDate has both a date and a time because you may be interested in analyzing order volume for different parts of the day. For the two other dates, only the date is required. A table validation rule for ShippedDate and PaidDate ensures those dates are not before the OrderDate. |
|
OrderStatusID |
The order status indicates where the order is in the Northwind workflow. Orders move through four phases: New —> Invoiced —> Shipped —> Closed.The foreign key for the current OrderStatus uses OrderStatusID from the lookup table of OrderStatus. Using a Status lookup table ensures that only the four pre-defined statuses can be assigned to an order. |
Order details table
This table stores information about Northwind’s order details.
|
Fields |
Description |
|
OrderID |
Each line item in the OrderDetails table must belong to one Order in the Orders table. OrderID is a foreign key identifying that order. As noted earlier, one order containing one or more line items illustrates a one-to-many relationship. |
|
ProductID |
Each record in the OrderDetails table includes the ProductID for the Product ordered. ProductID is a foreign key in the OrderDetails table, identifying that Product in that order. This is also a one-to-many relationship. |
|
OrderID+ ProductID |
As you saw in the Employees table, multiple fields can have a unique index. The unique index over OrderID+ProductID in the OrderDetails table ensures each order contains a product only once. When you open the Indexes property sheet from the ribbon you can see this unique index. |
Products table
This table stores information about Northwind’s products.
|
Fields |
Description |
|
ProductCode |
In addition to the primary key, ProductID, Northwind products have a human-friendly, uniquely indexed, product code. Employees normally refer to product codes not primary key values. The product code is a composite value consisting of a Category designation and a number, for example, B-1 for "Beverage", product 1. |
|
Product Name, Product Description |
In addition to short text product names, a long text description applies to products. This value could be used in a catalog description or to answer customer questions. |
|
UnitPrice |
All products are sold with a unit price for each item which simplifies the database as a showcase of features. In most real-world situations, pricing is often significantly more complex. |
See Also
