The Importance of Good Database Design

As more and more businesses start to look at online systems that are tailroed to the needs of the business, it is inevitable that this will result in custom database design and tailored solutions.

While tailored solutions are great for increasing effenciency and streamlining operations, the server that your online system is hosted on can very quickly come under pressure and start to lag if you don't follow some of the fundamental principles of database design.

So what exactly are some of the fundamental principles of good database design and how can you apply this to the day to day.

Database creation is essentially about creating a series of spreadsheets that hold different pieces of information. Some of these pieces of information must be shared and used in other tables.

The entire needs of your company must be setup in this format and when creating one of the tables you will always create require a unique / index column. This is the column that counts the number of records in the table and is a unique number for the row on that table.

When first creating a database it is ideal that each of these index columns be named slightly different but still identifiable. If for instance you are creating a table that is designed to track clients you may want to the 'clientname', 'clientemail' and 'clientphone'. The index column on a table like this might be named 'clid'. A trim of 'Client ID'.

If you then want to create a project table to track work being done for that client you would then want tp create a table that had columns that captured the 'projectname', 'projectduedate', 'projectdescription' and 'clientname'. Rather than having a column that copies the text from the 'clients' table into the 'clientname' field on the 'projects' table, you should simply create a link between the tables using the 'clid'. By using the 'clid' on the projects table any changes that are made to the 'clientname', 'clientphone' or 'clientemail' will be automatically 'copied' to the projects table. This isn't really a copy but a link between two tables. You can combine the tables into a single table via a join function and are then able to share the information.

Now this may seem like a very common practice however one thing that many database designers fail to account for is updating the indexes and keys on your database to minimise the number of queries and matches that must be run when linking tables. A good rule of thumb (but by no means an absolute) is that if you are using a column on one table that is a primary column on another table, you should always index that column.

In mySQL for example, this would be achieved by viewing the structure of the 'projects' table, selecting the 'clid' column and then clicking on the 'Index' icon at the bottom of the list.

This would set 'clid' as an indexed column and when a match is run across the tables, the database will not have to look at as many records and will have a lower resource requirement per query. This provides for greater stability across multiple users on a shared server and can significantly increase the lifespan of your server.

If you want to see a good example of this can be shown by get PHPmyAdmin to 'EXPLAIN' the query so we can view the resources usage of a non-optimised setup versus an optimised setup.

Firstly we will view the contents of our 'clients' table. You can see 5 demo clients, each with a unique 'clid'.

Now you can view the projects table. 5 projects with the first two assigned to clid 1, the third and fourth assigned to clid 2 and the final project to clid 5.

This is the EXPLAIN function of phpMyAdmin and you can see on the 'clients' table it had to look at one row while on the 'projects' table it had to look at all 5 rows to find the results. If you remove the 'EXPLAIN' from the start of this query you would simply see the results.

If we view the structure of the projects table you can see the 'indexes' for the table.

You can now select the 'clid' column and assign it as an index.

You will notice that the 'clid' is added to the indexes for the projects table.

Running the same query as earlier, you can see that on the clients table is completed one row check and was ready to move to the project table while the projects table then only had to check over two rows of data.

As you can see from this very small database of 5 records each in two tables, the number of matches used on the second table was dropped to half. If you had a table that had 100 projects with 200 clients, having to only match one row on the first query and then just the 2 or 3 that are on the second table is much quicker and more effecient than searching across all 200 records in the second table, which would be the case had you not indexed on the second table link.

It is certainly not recommended that you index every column in your database either as indexing is essentially you saying to the system "this column will be referenced often so keep this in the active cache for me". As with anything, over loading you cache will only result in problems so it is about creating a sequence of tables that allows you to link the common primary columns in the database as these will always result in an increase in effeciency.