The way you design your tables can determine how successful your database is going to be. These are a few of the lesser known secrets that can help you create tables that are easier to manage and maintain.
Microsoft Access is built from tables. It’s nearly impossible to over emphasize how important it is to make sure you design your tables in a way that makes them easy to use. Tables are the backbone of your system and when they are poorly designed, all of your data becomes suspect.
This is true not only for the way the data are entered, it is true for the maintenance too. These three tips are little known secrets that can help you control your data. You can also implement it on older databases, but you will need to be careful when you do.
1. Creating Parallel Names Indicating Table Relationships
This is an incredibly simple yet often overlooked tip. When people create tables, they don’t really think too much about the names. By putting a little bit of thought into your table names, you can simplify many different aspects of Access.
If you provide your child tables with a name that links to the parent table, you make queries and reports much easier to manage. You can tell by the names of the different tables how they are related.
To make the table names parallel, use an acronym of the name of the parent table at the beginning of all the child tables. For example, the table Client Information can have a child table with the name CI_Addresses. The beginning of the name points to the parent table. When you create a query or run a report, you can easily determine which tables you need to include in the search.
2. Creating Parallel Index Field Names
Just like tables, you can make your indexes easy to review by making them parallel. Field name duplication is not allowed. However, you can change the default name. Make the name parallel to the table name.
Notice you can also move its position in Design View. Move it up so that it appears first in the Table Design View. That makes it easier to identify it when you need it.
3. Working with the Join Type Option
Tables work best when you join them. You can control what kind of tables can be created based on the relationship of the parent record. For example, you can set the type to Cascade Delete Related Records to prevent the creation of orphan tables.
This function is really easy to use, but many people over look it. Take the time to figure out how you want your tables to be managed and linked. Once you know, make sure you set this option. Keep in mind that you should not need the Cascade Update Related Fields. Data should only be tracked in one field, not several. If you find you do need this join type option, you may want to consider redesigning your database at a later time.
When the Data Is the Problem
If you are working with an older database and encounter problems after trying to align tables with these suggestions, you may need to contact someone who can perform Access data recovery. It is best to get it resolved as quickly as possible by an expert. This ensures that you lose the least amount of data.
Victor Ren is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair xls data error and word recovery software products. For more information visit www.datanumen.com