Whenever you start considering a new project you will quickly find yourself wondering about the data model. At least if you’re the developer. Considering it early on helps building a good solution, and having a fine understanding of the caveats in data modeling is a great advantage.
Why is the data model important
The data model is important because it greatly impacts the future performance of your site or web app. During a user request to a normal dynamic website with PHP in the back end, it is not uncommon to execute a range of database queries. Poorly optimized sites may execute hundreds of database queries for every page load.
Providing users with a good experience is desired by all site owners, and being speedy at serving the content is one element of that. Therefore database query and model optimization should be a continuous process that starts before the first line of code was ever written. As with many other coding tasks, fixing the model later is usually an undesirable hassle of unknown proportions (likely containing huge problems).
What is a good data model?
The nature of the data model is that it is highly custom for a specific project, and therefore there is no way of generally saying what a good or bad model is. However, the fact that you even start considering it, will likely improve your project already. Do not ever just blindly start creating tables and attributes. Consider the relations between data and performance implications of field types.
Good data models adhere to a range of common standards and are generally well thought trough. With a fair amount of common sense and some technical knowledge about the database system you use, anyone can make a feasible model.
How to make a data model?
Building a data model is as simple as drawing or writing the tables, fields and types, and maintaining an overview over it. Remember to also do so while the model evolves and changes over time. Keep track of it. There are plenty of tools available for data modelling. One of the better ones is the free MySQL Workbench coming from the people who built MySQL. It gives a nice graphical overview of the tables, and provides you with easy to use editing functionality. At the same time it is easy to import/export SQL and use it on your database server.
Remember the data types
When designing your tables and fields, remember to pay attention to which data types you decide upon. There can potentially be huge differences in performance between the different field types. This can greatly impact over time when small amounts of data grow large. Also consider the field length, as there is no reason to make room for more data than you really need.
You should pay attention to indexes. They are settings in the individual tables of your model, which indicate what columns should be fast to query upon. The trick here is not to just index everything, but select the right things to index. Optimizing indexes can turn slow tables into fast ones again.
References & more reading
No comments yet (leave a comment)