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)

 H113 - Using MySQL Databases in PHP Pages
  [2561] The future of MySQL - (2010-01-03)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [947] What is an SQL injection attack? - (2006-11-27)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [915] Paging through hundreds of entries - (2006-11-05)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [647] Checking for MySQL errors - (2006-03-15)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [515] MySQL - an FAQ - (2005-12-03)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

S154 - MySQL - Designing an SQL Database System
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

Q916 - Object Orientation and General technical topics - Database design principles

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