Training, Open Source Programming Languages

This is page http://www.wellho.net/mouth/1423_.html

Our email: info@wellho.net • Phone: 01144 1225 708225

 
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
((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).

© WELL HOUSE CONSULTANTS LTD., 2023: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/1423_.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb