Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for 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
  [4426] FileMaker Day to Unix Time conversion - (2015-02-15)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)


Back to
West Wilts Rail User Group - Walk yesterday from Bradford-on-Avon to Trowbridge
Previous and next
or
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., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/3494_Dat ... lines.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat