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.

Planning a database of stores

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.

Planning a database of customers and orders

Planning a database of customers and orders

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) 23c9

Associated topics are indexed under

S154 - MySQL - Designing an SQL Database System
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [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)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [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)

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

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

4088 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 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).