Main Content

Database design - get it right from first principles

Archive - Originally posted on "The Horse's Mouth" - 2006-04-02 06:05:00 - Graham Ellis

It's VITAL to get your data(base) design more or less right before you write too much code - otherwise you'll end up wasting a lot of time and effort writing kludge code and - worse - forcing your users into work-arounds for the lifetime of the system at great expense to everyone in time, effort and sanity. But how DO you get the design right?

Start off with some sample data and apply Codd's principles of database normalisation - no table cells with multiple values, no repeated information and no calculated results to be stored ... and split the tables where necessary so that you can link them back together with a join when you present them. Sound complicated? Start off with a sheet of paper (or, better, a whiteboard) and draw up what you "really" want - and you'll soon spot where to normalise!

Then try out some test data and tables - through a very simple client like MySQL, with the SQL commands stored in a text file, since you can use those to seed your test databases later on.

At the end of last week, I ran a MySQL course and came up with an excellent normalisation example with one of the delegates. He has a number of clients (table) each of whom he produces a number of brochures for (table). Each brochure is available in a number of translations (table) and goes through a number of revisions (table) in each language. We also came up with a separate language table.

We then produced three test files of SQL commands (MySQL flavour) - one to flush old tables and create the new structure during experimentation, one to populate the tables with test data, and one to try out our multitable join syntax. NOTE - every table also has its own unique id column and, as a tip to help your sanity, use ids in different number ranges for each of the tables - on the test data, we started at 1, 101, 201, 301, etc. Name the id column "x"id where x is the first letter of the table name, and keep them unique.

You can see the table structure for the demo here
The test data is here
and the test join is here

Will the USER of the system want to see the data via this structure? No WAY - the user wants it formatted rather differently but that's where a programming language such as PHP comes in - to do the presentation layer. What the user wants to see is a report by client, with a matrix for each brochure showing rows for each language and columns for the latest revisions and their status. Have a look at this demo page and it will show you such a table.

We have the PHP source code available if you want to see how it's done (and a simpler example - source and running if you want to start gently).

Our customer has a long way to go with his system - date handling, blog handling for the texts, logins and authorities and many more subjects were discussed - but I'm confident that the basic table layout he has is correct and that the design is a robust one. And that's going to give him a system with a long and trouble free life.

See our MySQL FAQ for further tips and our MySQL resource index for loads more examples. Full MySQL documentation is available on the main MySQL web site