How to design your tables

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.

 

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Picking the perfect data type >>

Previous chapter: Adjusting the priority queue

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.