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

[James Courtier-Dutton]

On 8 October 2014 08:26, Martin A. Brooks <martin at hinterlands.org> wrote:
> On Tue, October 7, 2014 20:05, John Winters wrote:
>> Is there a good design reason to do this?
>
> I can sort of see why you might do this, but it's still wrong.  What
> they're trying to do, I think, is provide a consistent reference date for
> a set of related rows in other tables.  I would not do that in this
> manner, but if I were it'd be something like:
>
> create table dateref(
>   id serial,
>   d datetime with timezone default now();
> );
>
> and then use ID as a foreign key constaint for every table that needed a
> date reference.   The database does the work of enforcing the integrity
> and you can use things like cascading deletes to keep the whole thing
> clean.
>

But, we know you can do it this way, but the original question was "is
there a good design reason to do this?", and i would argue that for
dates, there is no good reason to do it.
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.

Kind regards

James