9.12     How to design your tables

This is NOT the latest copy of this book; click here for the latest version.

Designing unwieldy database tables is remarkably easy to do, as you can clearly see if you go to any company where a non-technical person uses Microsoft Access to store their data. While monolithic, "hack"-style databases are okay, they will not give you the best performance, and certainly will not operate at maximum efficiency.

We have already looked at normalisation, so you should be aware of how to design your tables so that they are dependent on each other for information. However, there are other factors to keep in mind when designing your tables - what to choose as your primary key, what data types to use, what to index and what not to index, etc.

Owing to the variety of database possibilities, large amounts of this information needs to come through experience. However, experience database designers will usually tell you that the best way to get started with a solid design is to treat your table as a thing in real life - to model it around the real-world. For example, if your table needs to store information about members of a club, the absolute easiest way to begin is just to take the fields from the paper membership form and create table fields out of them. Yes, you will need to do more work such as assigning keys, creating indexes, and adding other implementation-specific attributes, but it is a good start.