Open Stack

Excerpts from Mike Bayer's message of 2015-11-22 11:59:06 -0800:
> 
> On 11/20/2015 04:33 PM, Clint Byrum wrote:
> > Excerpts from Mike Bayer's message of 2015-11-20 11:29:31 -0800:
> >>
> >> On 11/20/2015 11:19 AM, Alexis Lee wrote:
> >>> We just had a fun discussion in IRC about whether foreign keys are evil.
> >>> Initially I thought this was crazy but mordred made some good points. To
> >>> paraphrase, that if you have a scale-out app already it's easier to
> >>> manage integrity in your app than scale-out your persistence layer.
> >>
> >> I've had this argument with mordred before, and it seems again there's
> >> the same misunderstanding going on:
> >>
> >> 1. Your application can have **conceptual** foreign keys in it, without
> >> actually having foreign keys **for real** in the database.  This means
> >> your SQLAlchemy code still does ForeignKey, ForeignKeyConstraint, and
> >> most importantly your **database still uses normal form**, that is, any
> >> row that refers to another does it based on a set of columns that
> >> exactly match to the primary key of a single table elsewhere (not to
> >> multiple tables, not to a function of the columns cast from int to
> >> string and concatenated to the value in the other table etc, an *exact
> >> match*).   I'm sure that mordred agrees with all of these practices,
> >> however when one says "we aren't going to use foreign keys anymore",
> >> typically it is all these critical schema design practices that go out
> >> the window.  Put another way, the foreign key concept not only
> >> constrains data in a real database, just the concept of them constraints
> >> the **developer** to use correct normal form.
> >>
> > 
> > Mike, thanks for making that clarification. I agree, that conceptual
> > FK's are not the same as FK constraints in the DB. Joins are not demons.
> > :)
> > 
> > To be clear, while what you say above is all true, normal form isn't
> > actually a goal of any system. It's a tactic one can use with a well known
> > efficiency profile. But there are times when it costs more than other
> > more brutal, less civilized methods of database design and usage. If we
> > don't measure our efficiency, we won't actually know if this is one of
> > those times or not.
> 
> 
> Hi Clint -
> 
> To be clear to be clear (  :)  ), I am absolutely fine with:
> 
> 1. disabling particular foreign key constraints on the server that are
> shown to be a performance bottleneck
> 
> 2. using denormalized forms where appropriate
> 
> 
> My only point is, the *starting position* for any new database construct
> should be favoring reasonable normalized forms and foreign key
> constraints, with disabled constraints and denormalized forms the
> exceptions, when shown to be appropriate.
> 
> What I don't want is, "we don't use foreign keys, they suck", and you
> then see the growth of all the wacky unmaintainable schema designs we
> see all the time in the PHP world and to some degree the ROR world -
> straight up incorrect designs, which as I mentioned earlier, include
> things like a series of columns related to another one only via some
> series of functions like string concatenations and casts, so-called
> "polymorphic foreign keys" where a particular column matches to that of
> a row in any one of several other tables, etc.   My userbase in the
> SQLAlchemy world is always coming to me with, "I have this terrible
> legacy schema design where...." and then they have something like this
> going on which they're struggling to work around.
> 
> Perhaps my point is, if we make a blanket "we don't use foreign keys"
> statement, then you just lift one of the last beachheads we have against
> folks who just don't know what they're doing implementing unmaintainable
> database designs.   Hence I think the FK concept should remain important
> and central when we work with relational schema design in Openstack,
> subject to exceptions as appropriate on a case-by-case basis.
> 

Well said. I think the key point to make is _we should use well
understood database designs._

What I think has happened many times, and I'm guilty of this too, is
that we get into a habit of thinking on a single trajectory. When we do
that, the things that look like they have bisected and disturbed similar
trajectories in the past are presumed guilty. Since I'm thinking in
several directions on a scalability plane, FK constraints make me twitch
a bit because I've been in a position to make large gains in scalability
by dropping them in the past. But I think it's prudent to maintain
discipline, and find the _actual_ limits of our database usage first.