DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Relational Database Design Guidelines

Creating a simple database in Access is very easy and everyone can do this job in a few clicks. But making a good relational database is not so simple. If you can design a simple database in Access, then you should read this article to get the idea of how to design a relational database for improved performance. In this article we shall briefly review how to design an efficient database in Access.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

While designing a relational database, it is a good idea to distribute the information in multiple tables. It is not advisable to store all the information in a single table, although it is easier to design. When your database grows in size, the efficiency decreases accordingly, For example, if you are planning to make a simple database of your employees, then you can divide the employees information in 3 tables. In one table you can store the contact info, in second you can store the salary and department details and in third table you can store the bonus history of the employees.

(article continues after sponsor spot)



An important part of a good database design is the proper selection of the primary key. The primary key must be unique within one database. Two or more tables in a database should not contain the same key as primary key, but if some table is the subset of another table than this may be done. You must know that the primary key is always unique for every record. My personal practice is to use an autonumber field for the primary key, rather than user defined data. The primary key is only used by the Access program to link one table to another one in the relationship, so humans do not need to know the values of the primary keys.

Every table must have its own primary key. All the tables must reference one another by a foreign key. For example, your main table has a primary key called emp_id, then this same field can be added in the other 2 tables for referencing. The emp_id field in the other 2 tables will not be the primary key. There are called the foreign keys. This makes it easy to retrieve the related records.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

Following the above structure will make it easier the proper and fast searching, editing and deleting the records. For example if you only need to edit the address of some employee then you only need to edit the address table.

Proper indexing will also increase the efficiency. You should index all the fields that are extensively used as searching criteria. More on this in a future article.

These few tips will really boost the performance of your Access database. The next article about relationships will go into more details, with some real life examples, and a sample database to follow along with.

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy