For 2023 - 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))
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.
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
join aisle on sid = a_sid)
join product on aid = p_aid)
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 articlesS154 - MySQL - Designing an SQL Database System 
MySQL - Pivot tables - (2004-09-22) 
Binary Large Objects or bars - (2005-06-27) 
Oops - I got my initial database design wrong - (2005-07-12) 
MySQL - a score of things to remember - (2005-11-12) 
MySQL - an FAQ - (2005-12-03) 
Database design - get it right from first principles - (2006-04-02) 
Databases needn't be frightening, hard or expensive - (2006-11-08) 
Display an image from a MySQL database in a web page via PHP - (2006-11-22) 
Code quality counts - (2006-11-26) 
Database design for a shopping application (MySQL) - (2008-03-15) 
More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24) 
What a difference a MySQL Index made - (2009-02-25) 
MySQL - licensing issues, even with using the name - (2009-03-16) 
Images in a database? How big is a database? (MySQL) - (2009-05-28) 
Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02) 
SQL - Data v Metadata, and the various stages of data selection - (2011-04-29) 
Blowing our own trumpet - MySQL resources - (2011-07-18) 
Databases - when to treat the rules as guidelines - (2011-10-23) 
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
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
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).