Re: Proposed feature: Selective Foreign Keys

Tom Dunstan wrote:
> The Problem
> -----------------
> One case that traditional SQL doesn't handle very well is when you have a child entity which can be
> attached to a number of different parent entities. Examples might be comments, tags or file
> attachments - we might have 20 different entities in the system that we would like our users to be
> able add comments to, but the existing solutions for mapping this all have downsides.
>
> Existing solution 1: Join tables ahoy
[...]
>
> Existing solution 2: Enter the matrix
[...]
>
> Existing solution 3: Embed the matrix
[...]
>
> Existing solution 4: Abandon ye all referential integrity
[...]
>
> Existing solution 5: Everything's a thing
[...]
>
> Basically none of the above handle the situation very well. The cleanest is solution 4, but lack of RI
> sucks.

I personally think that options 3 and 1 are the cleanest ones, but I
agree that they are not entirely satisfying.

I could think of a sixth option:
add a nullable column to each table that need a comment, tag or whatever.
You could use the same application logic for each of these columns, but
particularly for things that are more complicated than mere comments it
might be nice to have them centralized in one table.

> Feature Proposal: Selective foreign keys.
> -------------------------------------------------
> Allow foreign keys to have where clauses. The above comment example using solution 4 might then look
> like then following:
>
> CREATE TABLE comment as (
> id bigserial primary key,
> content text not null,
> parent_entity regclass not null,
> parent_id int8
> );
> ALTER TABLE comment ADD CONSTRAINT comment_blog_fk FOREIGN KEY (parent_id) REFERENCES blog(id) WHERE
> (parent_entity = ‘blog');
> ALTER TABLE comment ADD CONSTRAINT comment_event_fk FOREIGN KEY (parent_id) REFERENCES event(id) WHERE
> (parent_entity = ‘event');

> Comments?

I didn't read the patch and I cannot comment on how easy it would be
to implement this and what the performance impact might be.

What strikes me is that since foreign key constraints are implemented
as triggers in PostgreSQL, this solution would probably not have many
performance benefits over a self-written trigger that implements the
same functionality. Since you need two triggers for your example,
the performance might even be worse than a single self-written trigger.

Now performance isn't everything, but that would mean that the benefit
of your proposal is entirely on the usability side.

I personally don't think that it is so difficult to write a trigger
for that functionality yourself, but I guess that the argument for
this feature rests on how coveted such a functionality would be
(to justify the trade-off in code complexity).

Maybe one measure would be to figure out if any other relational
database system has implemented such a functionality. If there is
more than one, it might show that there is a certain demand for such
a feature.

Of course that's only circumstantial evidence; I guess that a
better measure would be how many people speak up and say
"I have always wanted that".

Yours,
Laurenz Albe