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)

 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 or Horse's mouth home

Forward to Looking forward - Chamber of Commerce has 2012 and beyond on the agenda

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