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

[Bernard Peek]

On 08/10/14 18:48, John Winters wrote:
> 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)

 From the information given we can surmise that the data represents 
individual days. I would not build a database without verifying that 
this will always be true. The cardinal rule of data modelling is "Know 
thy data."