Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
Databases - when to treat the rules as guidelines

One of the "rules" of database design is that data should only be stored once. Another is that calculated values should not be stored - they should be calculated every time you ask for them. That way, you are going to get consistent results from database enquiries. Of course, if the data you've stored is wrong your results will be consistently wrong, but that then makes the errors easier to find and once you're fixing them you can be sure that a single fix will put everything derived from it right.

Except ...

At times, it's not so much a rule, more a set of guidelines (I remeber that line from the "Pirates of the Carribean" - told in relation to the Priate's Code). Let me give you a couple of examples.

1. Caching results. Let's look at some queries ona web site:
"What time is the next train to Swindon" [Looks it up] "19:47"
"What time is the next train to Swindon" [Looks it up] "19:47"
"What time is the next train to Swindon" [Looks it up] "19:47"
"What time is the next train to Swindon" [Looks it up] "19:47"
"What time is the next train to Swindon" [Looks it up] "19:47"
"What time is the next train to Swindon" [Looks it up] "19:47"
Hang on ... we're repeating ourselves here and may be using up an awful lot of resource in the process. We would do far better to store the result in some sort of temporary cache which gets cleared out when the source data changes. Many databases are "read mostly" after all.

2. Where results are multiple short fields from a record that contains one or more huge fields. For example, the images in our picture library are stored in a database table, with each image having fields such as a brief description, an image name, and an id (all of which are quite short) ... and each record also contains a longblob which is the image itself. That's a great structure for looking up individual images, but it makes searching very slow indeed (the whole 500Mb database needs to be read and that leaves the server discbound) so we have duplicated the small columns into a smaller table - less that half a Mbyte - so that we can search and work with the control data easily. And - again - we re-duplicate this data when new images are added to the database (perhaps 2 or 3 times a day) or descriptions are changed.

To give you an idea of just how much difference this can make ... I've added the extra table to our picture database over the weekend, and the average job queue length on our server at any time has halved. It remains to see how much effect this will have when weekday traffic levels resume tomorrow morning!

In both cases, you'll note, we have designated an authoritative data source and the data that is authoritative remains unique. In that way, we can ensure that any errors can still be fixed at a single point, although an extra process is now required (and that may be as simple as setting a flag to indicate taht derived data needs to be regenarated) upon data change. Or - think of it another way - we're only writing our own caching or indexing system to provide more tuned caching / indexing than the underlying database would provide.
(written 2011-10-23)

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)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [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)
  [4426] FileMaker Day to Unix Time conversion - (2015-02-15)

Back to
West Wilts Rail User Group - Walk yesterday from Bradford-on-Avon to Trowbridge
Previous and next
Horse's mouth home
Forward to
Looking forward - Chamber of Commerce has 2012 and beyond on the agenda
Some other Articles
Which West Wilts Town? A picture quiz for you
Not the same language - but based on the same constructs
Melksham - Business to Business Speed Networking - build ahead for 2012
Looking forward - Chamber of Commerce has 2012 and beyond on the agenda
Databases - when to treat the rules as guidelines
West Wilts Rail User Group - Walk yesterday from Bradford-on-Avon to Trowbridge
Upcoming events in and about Melksham - more dates for your diary
Who is knocking at your web site door? Are you well set up to deal with allcomers?
How not to call when job seeking ...
Python courses and Private courses - gently updating our product to keep it ahead of the game
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

© WELL HOUSE CONSULTANTS LTD., 2022: 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/3494_.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb