Main Content

Database design for a shopping application (MySQL)

Archive - Originally posted on "The Horse's Mouth" - 2008-03-15 09:19:39 - Graham Ellis

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!