Top 10 database design tips
When I am asked to review an application written in Access the first thing I look at is the database tables. The design of the tables and their relationships will tell me a lot about the standard of the database. I consider the database table structure as the foundation of an application just as you would the foundations of a house. We all know what happens when you build on a bad foundation, and to do so in a software application is to invite trouble.
It is important to think of how the data will be retrieved before you consider how it will be stored. Even if you get it wrong and your application has gone to production, don’t be frightened to re-engineer the design … it’s worth the effort in the long run!
Whilst I will address my table design tips specifically to Access, the same can apply to any database.
1. Table design and naming
How often do you see a table with unrelated fields? A database table should logically contain fields that are related to each other. eg. In the employees table I would expect to find information about employees not suppliers. Sounds simple enough but you would surprised how often this simple design error is made. Your table name should be meaningful, not too long and preferably with alphabetic characters. eg. table PROJECT – HEADER Details would be better named ProjectHeaderDetails or just ProjectHeader.
2. Field (column) naming
Just like table naming the field name should be relevant and easily understood. It is generally desirable to use a combination of upper and lower case alphabetic characters. eg. field patient name would be better expressed as PatientName.
3. Data types
Access has various data types such as text, number and date. I see many examples of numbers stored in a text field. If the field will only ever contain numbers then, why allow text? This can not only cause problems for sorting and calculating, but opens the door for data integrity issues.
4. Table level validation
I like using data validation at the table level because it can prevent data errors when people are entering directly into the table. Yes, we all know that basic data entry and validation should be controlled by the software, however IT support staff and significant others will usually have direct access to database tables … sound familiar?
5. Referential integrity
This is one of my favourites because it is so crucial to maintaining database integrity. RI should ensure that you never have orphaned records in your database and, if you turn on cascading deletes then you can be sure that all child records will be deleted along with the parent. Unlike other relational databases I have worked with in the past, this feature is built into Access and most modern databases.
6. Indexes – when and why?
Indexes will improve retrieval of records but there is an overhead for updating. Think about the field and how often it will be accessed. Does it need to be indexed? Indexing can also be useful to force unique data in a field that is not a key or part of a composite key field.
7. Table splitting – use sub-tables as required
Some designers see their database tables as spreadsheets and cannot fathom one-to-many relationships. Invoices are a good example of records that typically need to be split into more than one table. There is the header record which has the date and debtor details and then there are the line items with quantities and cost. So there we have the classic one header record to many line item records and thus, two tables. Don’t unnecessarily repeat data in your table because you are too lazy to create a sub-table.
8. Normalisation
This sometimes comes down to a personal preference as to the degree of normalisation in your database. Like most things, there is no absolute right or wrong. My preference is for considerable normalisation to avoid database bloat and generally improve performance.
9. When is it ok to “hard code” data
Whilst it is generally accepted that “hard coding” of data is wrong, I have found occasions when it is desirable. Sometimes you just want to avoid a plethora of reference tables which only contain one or two records. In that case, I use one Function that will return a string according to the code and field reference arguments. I use this approach only for reference data which is unlikely to be changed.
10. How many fields in your table?
Last but not least, and certainly worth a mention. A friend of mine wanted to learn Access and set about designing a database to record the results of a cricket game. After some time he came back to me to ask for help. He said everything was going well except that Access would not allow him to add any more fields to his table. I asked him how many fields were in the table and he said 255 … I sat him down and explained that a table should contain like records and that we needed to break his one table into a number of different tables! Needless to say, he did not pursue a career as a programmer.
As I said earlier, there is no absolute right or wrong way to design database tables. Some situations require you to break the normal rules of good design. Spend the time necessary to plan an optimal table design and you will reap the rewards as you build your application and continue to support it.
What’s not to like about Access How to insert old records into an Auto Numbered table