Access Table Design Tips
Use table normalization whenever possible. Normalizing tables is highly recommended since it will reduce several tables into a few tables. Refer to the features section on how to normalize data.
Use dropdown lists for text fields that should limit the user to a predefined list of values. For example, when entering an address, use a dropdown list for the ‘State’.
Avoid using memo fields if possible. Memo fields can make Access databases become unstable in certain situations.
Use indexes since they enable the database to quickly access data once it is normalized. Every table should always have an index. If a table does not have an index, it will take the database longer to pull the information. Make sure to set an index with no duplicates.
Use one or more data fields that are not of auto number type when creating an index. This will save time when trying to manually work in data tables.
Avoid storage of calculated values in the table. An application example is a billing system with an invoice table. Do not store the invoice total in the table. The total can be easily calculated by adding up all the items in the invoice.
Use the Unicode Compression attribute. Go through the database and make sure text fields that are more than 3 characters in size are set to 'Yes'. This will help the Access database use less hard drive space.
If you haven't time to implement these tips or if the development resources available to you are unable to make progress, please contact us.
Table Relationships >
By Admin at 16 Aug 2016, 12:23 PM