Interested In The Golden Rules For Building Access Databases?

You can, by following a few golden rules, build Access tables and relationships which use your data in the most efficient way possible. These rules are often called Normalisation Rules and by following them you can learn to build Access databases which allow you to query and display results in the most effective way possible.

Sometimes when building a brand new database you might want to create one big table listing everything imaginable you might possibly want to store. Of course in Access you learn that the "one huge table" approach does tend to mean you have to repeat the same data in separate records.

For example if you are creating a shop ordering system your table might contain headings for Customer, Customer Address, Order details etc. However, for each new order you add to this table you have to add the customer name and customer address every time they make an order, even though the customer may always have the same name and address details.

So these rules were devised to guide the database designer in building separate but related tables to eliminate such duplication. The rules help with effective table design and the resulting tables are linked through Primary keys and Foreign keys.


The first rule - create separate tables for each set of related data.

Each table is about one specific subject and each table has an index heading or primary key, used to identify each table record. So you end up with several or many small tables rather than that one huge table we first started with. The tables are then linked together in relationships through the primary keys so that an Access query can lookup data from more than one table at a time to produce composite information such as for a customer invoice.

For example a sales order system could have separate tables for customers, orders, staff and suppliers, each with their own primary key. In the Orders table, as well as having its own Orders ID primary key, we include a heading for Customer ID to identify the customer (from the customers table) and a heading for Staff ID to identify the member of staff who took the order (from the staff table). We can create an Access query to build an invoice by looking up data from the customers, orders and staff tables each time an invoice is required.


The second rule - only have data in the table directly relevant to the record.

Each table column must be a necessary part of the table record. So if one or more columns are not strictly required for each record, then these can be moved to a separate table and linked through its primary key to the first table.

For example if an Orders table contains the product ID and the product name we can conclude that the product name isn't really necessary as just the product ID will do. So we should create a separate Products table listing all the products available with their product IDs as the table's primary key and in the Orders table just have the Product ID. We link this to the primary key of the Products table, so a query could look up the Product name from the Product ID in the Orders table.


The third rule - eliminate table headings not dependant on the table's primary key. Note this is a little different from the second rule.

Suppose for example in this new Products table you've added the headings "Supplier" and "Supplier Location" because you want to list the source of each product. The supplier location hasn't anything to do with the Products primary key. This rule suggests it's better to create a separate Suppliers table with its own Supplier ID and just put the heading Supplier ID in the Products table. A query can look up the supplier details from the Product table Supplier's ID.

Once you've created your tables in Access, each with its own primary key, some tables will also contain foreign keys. These are headings such as IDs which refer to data in other table. For example the Orders table has foreign keys for Customer ID and Staff ID as we described earlier.

In Access you can use the Relationships View to create links between tables. You can tell Access which tables to display, and Access shows all the table headings for each table. We can then drag a primary key, for example from our Customers table, to the Orders table, to the corresponding foreign key in the Orders table. This creates a link between the tables and tells Access the tables are related, so we can subsequently use a query to look up data from more than one table at a time.

These three golden rules, or normalisation rules, help us create smaller tables related through primary and foreign keys resulting in efficient data storage and querying. To learn more about effective database design using Access consider attending a training course to really develop your skills.

---

Author is a freelance copywriter. For more information on 2002 access microsoft training, please visit http://www.microsofttraining.net

---