Data Models

Data Models are the least understood part of an Access database. The diagramme below is a portion of the SQL Server version of the Northwind Traders database provided as an example by Microsoft.

The whole point of the model is to join up the tables so that there is only one place where a data item is defined. Because it is defined in one place the spelling is correct and from the moment it is entered into the table all other tables that need to use that item of data will get it from that place.

In the diagramme, you can see that the Customer’s table defines a Customer ID. This is a key field maintained and issued by the database. When an order is placed, the Customer ID is taken from the customer’s table and cross referenced into the orders table. The line connecting them is a business rule that says “You can’t take an order unless we have the customer details in the database with the address, contact details etc written down correctly.

You no doubt have experience of trying to maintain related data in Excel. Keeping all the data correctly aligned become very difficult once you go to more than a thousand lines. This is the main reason why users turn to Access from Excel.