MySQL - table design and initial testing example

For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))

"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)

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

4759 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, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 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).

You can Add a comment or ranking to this page

Like this? ??