Here's a "proof of concept" demo from yesterday's MySQL session - a plan for the various tables that might be involved, and a few sample columns to see how "the whole" would work together.

On one side, you have a table of the shops that you're running the site for - the code is designed to be able to operate for multiple outlets with different products at the same time. On my diagram, that's called "takeaways" which may lead you to conclude we were talking about this just before lunch.

Each takeaway have a number of product groups - we used the term "aisles" to describe them as for ease of design we wanted each table name to start with a different letter and we didn't want to confuse categories with customers!

And then in each aisle you have a number of individual products.


That's the products for sale - those three tables are altered only by the system administrator as products and prices change.

Then on the other side, you have three tables which control the dynamics of the orders.

A customer table has a record for each customer.

Each order a customer places has a row in a separate table which can join back

And finally, there's an item table which lists all the items on each order. It also includes an id from the product table so that appropriate joins of the tables will identify what the products being bought actually are.


My habit of using a primary key called xid, where X is the first letter of the table name, should help to clarify the example - you can see where the joins need to be written using this technique, as you'll always want to link columns with the same names.

Comments / enhancements? Many! You'll want to add in all the extrafields for order stats and comments. A system that offers your repeat customers the ability to pick back through what they have bought before. Extra data to ensure that anyone who orders a curry is also offered rice or nan ...

The delegates on yesterday's course asked me to post this information up here - on all of our courses, we listen to our customers' specific needs and tailor the presentation to suit. It makes for much more relevant, effective training - even if the resultant proof-of-concept posts here make the graphic artists amongst us quake at the scruffy graphics!
(written 2008-03-15, updated 2008-03-16)

 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)
  [666] Database design - get it right from first principles - (2006-04-02)
  [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)
  [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)

Back to Joining MySQL tables revisited - finding nonmatching records, etc

Previous and next or Horse's mouth home

Forward to Making PHP and MySQL training relevant to the course delegates

Rome, and the faith of Rome
Please don't shout at me!
Spring and early summer training courses
Making PHP and MySQL training relevant to the course delegates
Database design for a shopping application (MySQL)
Joining MySQL tables revisited - finding nonmatching records, etc
Budget tax increases hit vehicle market
C - structs and unions, C++ classes and polymorphism
Await guests in the early hours
London Midland ... Merrymaker ... Percy Danks