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

[Bernard Peek]

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.