Database Design

Store Unique Database Information in One Place:

Most database developers understand the basic concept of data normalisation. Ideally, you'd like to store the same data in one place and refer to it with an ID when you need to reference it. Therefore, if some information changes, you can change it in one place and the information changes throughout your application.

For instance, a customer table would store a record for each customer, including name, address, phone numbers, e-mail address, and other characteristics. The customer table would have a unique CustomerID field (usually an Autonumber field if there is no 'natural' key) that is its key field and used by other tables to refer to the customer. Therefore, an invoice table, rather than storing all the customer information with each invoice (because the same customer may have multiple invoices), would simply refer to the customer ID value, which could be used to look up the customer details in the customer table.

Access makes it very easy to do this through its powerful forms that use combo boxes and subforms. If you need to make a change to the customer's information (such as a new phone number), you can change it in the customer table and know that any other part of your application that references that information is automatically updated.

With a properly normalised database, changes to data over time are easily handled with a simple edit. Improperly normalised databases often include programming or queries to make changes across multiple records or tables. This not only requires more work to implement, but it also increases the chances of the data becoming inconsistent if the code or queries don't execute properly.