Post: Drimnagh, Dublin 12, Republic Of Ireland

Tel/Fax: 353-1-4548335 Email:dbassist@indigo.ie

<<< back to Access Tips

   (This page is worth printing and reading with a cup of tea!)

When designing a database you are faced wth a number of questions: How many tables will there be? What will they represent? Which columns (fields) will go in which tables? This page is intended to make the answers to these questions easier if not automatic.

There's never enough time to do it right first time, but always enough time to do it over?

Trial and error may get you there, but may also get you certifiable. Frustrations, confusions and anxieties can be eliminated with good planning. One valuable (if not indespensible) planning tool for designing databases is called Normalisation.

Normalisation is the process of simplyfying the design of a database so that it achieves optimum structure. It implements a theory called Normal Forms - a linear progression of rules you apply - with each higher 'normal form' acheiving a better more efficient design. This page will bring you as far as the Third Normal Form - there are more, but if you apply these three you are well on your way.

First Normal Form (1NF) - All column values must be atomic

These means that in your table, for every row-by-column position (cell), there exists only one value - not an array or list of values:

1NF also prohibits the presence of repeating groups, even if they are stored in multiple columns:

To attain 1NF, we have added a column [OrderItemID]. Now, the primary key of the table is the composite of [OrderID] and [OrderItemID]

Second Normal Form (2NF) is already in 1NF and each non-key column is fully dependent on the (entire) Primary Key

This is saying that tables should only store data relating to one 'thing' and should be fully described by it's primary key. 

In our example above, the primary key is the composite of  [OrderID] and [OrderItemID], so, does this (combined) value for a given record imply the value of every other column in that record? No it doesn't - given the value of just the [OrderID] field, you know the customer and the date, without having to know the [OrderItemID]. So neither the [CustID] field nor the [Date] field is dependent on the (entire) primary key.

We can acheive 2NF by breaking our table out into two separate tables.  We put everything that applies to each Order in one table and everything that applies to each Order Detail in a second table. This is called decomposition.

We decompose these tables in such a way as to allow us to put them back together using queries.

Thus it is important to make sure that tblOrderDetails containsa foreign key a to tblOrders.

The foreign key in this case is the [OrderID] field which appears in both tables.

Third Normal Form (3NF) is already in 2NF and all non-key columns are mutually independent.

In our example here, tblOrderDetails is in 2NF because all its non-key columns are fully dependent on the primary key. However, the table also contains a dependency between two of its non-key columns: [ItemID] and [Item].

Suppose you need to add 150 detail records, each involving the purchase of screwdrivers. You will have to input an [ItemID] of '2' and an [Item] of 'Screwdriver' for each of these 150 records. Similarly, if you decided to change the description of item to "No. 2 Philips-head Screwdriver", you will have to locate and update every singly record in your database.

This is where 3NF comes in handy. You further decompose the table by breaking out the [ItemID] and [Item] dependency. This changes the tblOrderDetails and reates a new table called tblItems

Again, when composing, take care to put a linking column in both tables. In our case, the [ItemID] field becomes the primary key of the new table tblItems and the foreign key in the tblOrderDetails

Both these tables are in Third Normal Form (3NF)

The [ItemID] column is the primary key in the table tblItems and is now the foreign key in tblOrderDetails

We now have three well designed tables (instead of one badly designed one) that are easily linked with relationships and are easily maintained. We've eliminated data redundancy and avoided any inconsistent dependancy. (We've also had to cope with a few unusual terms - check out  Access glossary of words & terms )

[ Home ] [ Development ] [ Training ] [ Access Tips ] [ Excel Tips ] [ Philosophy ] [ Take A Break ]

Send mail to dbassist@indigo.ie with questions or comments about this web site.
Copyright © 2002 DBAssist
Last modified: November 03, 2002