Tips on Table design
The BEST reference I have ever read on this topic is Chapter #1 of Building Microsoft Access Applications by John L. Viescas. An absolute must read for every MS Access Developer.
1- Structure tables by following database design practices Normal Form 1 and Normal Form 2.
2 – Use Numeric data types correctly.
Numeric data types should be used if mathematical calculations can/will be performed on the values. Zip Codes, Phone Number, Social Security #;’s are string (text) data type – not numeric.
3 – Avoid using AutoNumber Primary Keys in place of unique identifiers.
Tables should have Primary Key fields that consist of real values. Simply throwing an AutoNumber field in every table is not a solution for identifying a Primary Key.
AutoNumbers should be used as Relational Keys ONLY, relating records from one table to another. Their values have no real meaning and should not be used as.a unique identifier whenever possible.
For Example, a properly structured customer table may look like:
tblCustomer
- CustomerID – AutoNumber (Relational Key)
- CustomerName – Primary Key
- EIN
- DateofEntry
4 – Do not use AutoNumber fields in Lookup tables.
Storing the value rather than an AutoNumber is more effective when working with Lookup tables. Lookup tables are defined as tables that generally consist of one or two fields and a limited number of values.
In this lookup table example, simply store the 2 character State value, no need to create an AutoNumber.
tblState
- State (text - size 2 PK)
- StateName (text - size 50)
5– Do not store calculated values.
Foe example, the amount of sales tax is calculated by Cost * Tax Rate. Cost and Tax Rate should be stored, not the amount of tax (it is calculated).
Note that storing calculated values can speed up the performance of an Access application. This method requires very careful planning and the creation of data validation procedures.
6– Follow a naming convention.
|
