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

[Bernard Peek]

On 07/10/14 20:05, John Winters wrote:
> I'm extracting data from a legacy (sort of - still actively sold) system
> which has the following table:
>
> (CSV dump of the table)
>
>
> Days,DateIdent
> 2000-01-03 00:00:00.000,1
> 2000-01-05 00:00:00.000,2
> 2000-01-06 00:00:00.000,3
> 2000-01-07 00:00:00.000,4
> 2000-01-08 00:00:00.000,5
> 2000-01-10 00:00:00.000,6
> 2000-01-11 00:00:00.000,7
>
> ...
>
> 2031-04-29 00:00:00.000,10382
> 2031-04-30 00:00:00.000,10383
> 2031-05-01 00:00:00.000,10384
> 2031-05-02 00:00:00.000,10385
> 2031-05-03 00:00:00.000,10386
> 2031-05-04 00:00:00.000,10387
>
> Then whenever another table in the database would normally have a column
> of type "Date" it has column of type "Integer", which is used to index
> into this table.
>
> I'm no database guru, but this seems to me to be bordering on the
> insane.  Apart from anything else, it means you have to error check
> every attempt to retrieve a record with a date in it, in case the date
> integer does not have a corresponding row in the Date table.
>
> Is there a good design reason to do this?

It's called a surrogate key and in the database world it causes lots of 
arguments. Some people always use a surrogate key even when they have 
(as apparently here) a perfectly good natural key. If you want the full 
rant Google for my name in the comp.databases.theory newsgroup. I've 
lost count of how many times I've been through the argument.