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
(written 2006-04-02 06:05:00)

 S154 - MySQL - Designing an SQL Database System
H113 - Using MySQL Databases in PHP Pages

Back to PHP Image viewing application

Previous and next or Horse's mouth home

Forward to Well House Manor goes ahead

Architectural Heritage - Devizes
The best Open Source library in town
Python - block insets help with documentation
Well House Manor goes ahead
Database design - get it right from first principles
PHP Image viewing application
Ruby course - oops - it's not happening
Python to MySQL
An unhelpful error message from Apache httpd
Dressed up for a phone call
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).