[GLLUG] Is there any sane reason to do this? (Database question.)

[John Winters]

On 08/10/14 18:46, Bernard Peek wrote:
> On 08/10/14 14:07, John G Walker wrote:
>>
>> On Wed, 8 Oct 2014 13:38:23 +0100 James Courtier-Dutton
>> <james.dutton at gmail.com> wrote:
>>
>>> There are all sorts of design reasons for laying out schema in
>>> particular ways, normally tables like this are to reduce duplication
>>> of data in the database, but there is always a trade off between less
>>> duplication vs. performance vs gap locking problems.
>> One reason for doing might be that a date associated with a particular
>> key might change. One change to the database would then change all
>> instances of the date.
> 
> To know whether that is a risk you need to understand the data. It
> appears that there is a 1:1 relationship between the date and the
> surrogate key. If you know for sure and certain that there will always
> be one record per day then you could, and IMHO should, dispense with the
> surrogate. If there is any doubt then using the surrogate is safer.
> 
> A case in point is national insurance numbers. There is supposed to be 
> a 1:1 relationship between NINO and people. More than one HR database
> has been crashed when a duplicate appeared.

So this is defensive design against the possibility of some year having
more than one 11th of August?

(and harking back to the original data, no 4th or 9th of January, 2000)

John

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 551 bytes
Desc: OpenPGP digital signature
URL: <http://mailman.lug.org.uk/pipermail/gllug/attachments/20141008/7d5b50b4/attachment.pgp>