MySQL - table design and initial testing example

"Examples are either simple as to be trivial, or too complex to understand" said a delegate - so this afternoon I accepted his challenge to come up with some thing in between. And the example is for some MySQL table design and design testing, with a Perl program to access and tabulate the data.

Scenario - A table of CUSTOMERS each of whom has a number of order REQUESTS each of which includes a number of line ITEMS. (3 tables so far!) A table of STORES each of which has a number of AISLES, each of which has a number of different PRODUCTS on it. That's six tables now. The items table contains a product id column, so that all the tables can be linked together.

3 tables to be joined

Customer, request, item tables

3 more tables to be joined

Store, aisle, product tables

# Note - all fields start with same letter in each table
# Note - all tables have a unique id field
# Note - all tables first letter in unique
# Note - all table names are singular
# Note - sample data includes 2 records at each level so
# that we can test the dynamics but keep it as simple as poss
(Don't you love comments - those come from the test file!)

Here are the results of various reports:

Result of joining 6 tables

Here is the "base" query behind all the joins and reports:

select c_name,p_name,i_count,p_price,a_name,s_name from

         ((store

         join aisle on sid = a_sid)

         join product on aid = p_aid)

         join

         ((customer

         join request on cid = r_cid )

         join item on rid=i_rid)

         on pid = i_pid ;

and you can see all the code used here. The output is here.

Finally - the Perl program's source is here.
(written 2007-11-06)

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)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [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)

56ac

Back to
Wiltshire - speaker / after dinner talker offer

Previous and next
or
Horse's mouth home

Forward to
Closer than you think - the next step

Some other Articles

Arrays in Tcl - a demonstration
Buffering up in Tcl - the empty coke can comparison
Melksham v Ely
Closer than you think - the next step
MySQL - table design and initial testing example
Wiltshire - speaker / after dinner talker offer
Castle Lodge Hotel, Ely, Cambridgeshire
The Learning Perl crew, October 2007
National Speaker - now to get the talk ready
A Golf Club Decision - Perl to Java