data modeling with access 2007

the Relational Model

Access is a relational database management system (RDBMS). Although it is small-business oriented, it is compatible with all its bigger counterparts such as Microsoft SQL Server, MySQL and Oracle.

The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to express database queries and enforce database integrity constraints.

The relational model was subsequently maintained and developed by Chris Date and Hugh Darwen among others. In The Third Manifesto (first published in 1995) Date and Darwen show how the relational model can accommodate certain desired object-oriented features without compromising its fundamental principles.

Designing the model

entities and attibutes

To study the basics of data modeling we'll look at a simple, common business application: the automobile repair facility, aka Garage.

The analyst/designer would first sit down with Mike for an hour or two and take notes on how Mike operates the garage and what he wants from the new system. Here is a somewhat abbreviated version of the summary the analyst would produce:

When a customer brings a vehicle in for service we'll open a work order based on the vehicle's plate number. Since each vehicle has only one owner we can keep track of the owner through the vehicle ID. When we create the file for a new customer we get all the basic info plus gender and date of birth, if the customer accepts to give it. We'd like to be able to analyze our sales to men and women and by age group to tailor our publicity better.

The work order is used also as an invoice to charge for all parts and service. There is always a technician assigned to a work order and he is the one reponsible for the work being done. When parts are needed they will be taken from stock. If a part is not in stock it must be ordered from the supplier. We must be able to immediately find the supplier's phone number and so on for each part that we have. In some cases there will be a discount allowed for a part that's on sale and that will show on the invoice.

The first task for the designer is to identify the entities and the relationships between them.

An entity is a thing (person, object, place) we keep information on.

In Mike's Garage, we can readily identify several entities: customers, cars, parts and suppliers. Each of those entities will have attributes, characteristics that describe the entity.

A customer entity has attributes: name, address, phone number, date of birth, etc.

A car entity has attributes: make, model, model year, plate number, etc.


We have an entity parts and an entity suppliers. What's the relationship between them?

Usually we'll express it in simple terms: the part comes from or is bought from a supplier.

We also have to determine the cardinality of the relationship: how many instances of the entities are involved. In this case, we'd note that: a part has only one supplier but a supplier can sell us many different parts. That called: a one-to-many relationship.

When we model that in the database, what we'll do is put the supplier ID in the parts entity so that we can call-up all the supplier's attributes for any given part.

Jumping ahead a little bit, this is what the relationship will look like in Access:

Now, when we look at the customer - parts relationship we have a different situation.

A customer buys several parts and a given part may be bought by several customers. That's a many-to-many relationship.

In the database there is only one way to indicate that type of relationship: there has to be another entity between those two.

the Master/Detail model

Without going into all the reasons why, let's just say that whenever you have a situation that can be described as a transaction containing several items, you will invariably end-up with two tables to express the relationship: a master table and a details table.

A cutomer orders several products -->Order - Order details

A cutomer is billed for several parts -->Invoice - Invoice details

A student registers for several classes -->Registration - Registration details

An employee has worked on several projects -->Timesheet -Timesheet details

We could go on and on. That situation is absolutely normal in business. All kinds of models can be described as Master/detail.

In Access (and SQL Server), the prime example of this is Microsoft's flagship database model called Northwind. You can install it from the templates and study it to see how it compares to our situation here.

For Mike's Garage, the data model that we'll end up with is illustrated here:

access relationships