Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
Database design for a shopping application (MySQL)

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)

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)
  [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
Horse's mouth home
Forward to
Making PHP and MySQL training relevant to the course delegates
Some other Articles
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
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., 2022: 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/1575_.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb