"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.
(written 2007-11-06)
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)
[1575] Database design for a shopping application (MySQL) - (2008-03-15)
[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)
Some other Articles
Arrays in Tcl - a demonstrationBuffering up in Tcl - the empty coke can comparisonMelksham v ElyCloser than you think - the next stepMySQL - table design and initial testing exampleWiltshire - speaker / after dinner talker offerCastle Lodge Hotel, Ely, CambridgeshireThe Learning Perl crew, October 2007National Speaker - now to get the talk readyA Golf Club Decision - Perl to Java