Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Database design - get it right from first principles

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, updated 2006-06-05)

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S154 - MySQL - Designing an SQL Database System
  [59] MySQL - Pivot tables - (2004-09-22)
  [361] Binary Large Objects or bars - (2005-06-27)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [515] MySQL - an FAQ - (2005-12-03)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [945] Code quality counts - (2006-11-26)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [4426] FileMaker Day to Unix Time conversion - (2015-02-15)

Q916 - Object Orientation and General technical topics - Database design principles
  [572] Giving the researcher power over database analysis - (2006-01-22)

H113 - Using MySQL Databases in PHP Pages
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [647] Checking for MySQL errors - (2006-03-15)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [915] Paging through hundreds of entries - (2006-11-05)
  [947] What is an SQL injection attack? - (2006-11-27)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2561] The future of MySQL - (2010-01-03)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4378] What FGW passengers want to talk about / and PHP programming to find out - (2015-01-01)
  [4483] Moving from mysql to mysqli - simple worked example - (2015-05-03)

Back to
PHP Image viewing application
Previous and next
Horse's mouth home
Forward to
Well House Manor goes ahead
Some other Articles
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
4759 posts, page by page
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, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 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).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/666_.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb