MySQL - table design and initial testing example

Archive - Originally posted on "The Horse's Mouth" - 2007-11-06 16:35:12 - Graham Ellis
"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.

Customer, request, item tables

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:

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.

Main Content

MySQL - table design and initial testing example