Open Stack

Adam Young <ayoung at redhat.com> wrote:

> On 03/10/2015 10:23 AM, Mike Bayer wrote:
>> if *that’s*
>>  what you mean, that’s known as a “polymorphic foreign key”, and
>> it is not actually a foreign key at all, it is a terrible antipattern started by
>> the PHP/Rails community and carried forth by projects like Django. 
> A) Heh. it is much, much older than that.  SQL Database have been around for long enough for these antipatterns to be discovered and rediscovered by multiple generations.  I'm aware of the mean by which we cn mitigate them. 
> 
> But that is not what we are doing here.  These are no "parity" issues even.  It is distributed data.
> 
> User sand Groups are in, not just one LDAP server,  but many.  With Federation, the users  will not even be in a system we can enumerate.  Which is good, we should never have been allowing "list users" in the first place.
> 
> What the Assignments table is doing is pulling together the User and groups from remote systems together with role defintions and project definitions in the local database.  The data is not in one database.  It is in Many.

Of course, if you are referring to data that is related to rows in a
*remote* database, either another SQL database or something else like an
LDAP, you need not have any kind of “foreign key” setup. This is normally
technically infeasible in any case unless you want to do remote schema
access (which you do not). Although I do recommend that referring to remote
databases be done using some kind of portable identifier, usually a GUID,
and not the auto incrementing integers that are local to the schema.

If I can summarize the proposed reasons to remove foreign keys, since my
original question was, "can you elaborate on your reasoning that FK
constraints should be used less overall?”, and I just want to make sure 
I’ve got my answer:

1. You have modified your schema such that you are no longer pointing to a
remote record, or that remote record is not in the local database. This
reason is perfectly fine.

2. You’ve benchmarked that the presence of foreign keys is making your
application measurably slower. This reason is perfectly fine, but I don’t
believe Keystone would ever see this (but since we’d be measuring, it
doesn’t matter what I think).

3. You still have tables/rows that refer to each other, but you either have
changed it to not refer to the primary key (note that at least Postgresql
supports foreign keys to UNIQUE columns), or it still refers to the primary
key but the FK is just removed for arbitrary, non-performance based reasons,
or you’re doing some non-normalized, relationally incorrect thing like a 
“polymorphic foreign key”.  I’m totally against these reason, but it does not seem 
like this is being proposed.  

So I think Morgan’s original idea was that, for those inter-table
relationships where we refer to something that could just as well come from
a remote datasource, we should not assume any foreign key is present. That
is great. My only point is, if for the time being there *are* two tables
there with a column that refers to the PK of the other, unless you’ve proven
a performance issue, a plain, non-CASCADING foreign key should be left in
place, even though the application should in no way assume this FK is 
present.   Obviously any kind of application logic that makes use of 
an integrity error to learn something about the database should be removed.