Re: [SQLObject] two postgres questions

[Magnus <ma...@th...>]

At 19:45 2003-05-28 -0400, Edmund Lian wrote:
>To be honest, I'd rather see synthetic primary keys banished. But, while 
>PostgreSQL allows changes in primary keys to cascade automatically to 
>foreign keys in other tables, Oracle and a few other databases do not. So, 
>the need to be portable might well necessitate synthetic keys.

Does *any* database but PostgreSQL support cascaded key updates
like that?

Anyway, this is *not* the big problem with "natural" primary keys.

The main problem is that the business rules change, and making big
changes in large, actively used databases is very disruptive and
expensive. We want to isolate each needed change as much as possible.

It's far from unique that the set of columns that was the ideal key
yesterday isn't that any longer. Yesterday it was a solid business
rule that only one row in this table could be created per person
each day, so personId + registryDate seemed to be the ideal combo.
The business experts are so sure that they would bet their arm on it.
Today things changed, and in a few cases, it might actually happen
that there have to be two rows for one person on certain dates.

With synthetic keys, this means that we drop a unique index. With
natural keys, our primary key breaks, and with that maybe a whole
tree of keys for detail and sub-detail tables that use these
fields as parts of their primary keys. Yuk! Been there, done that...
I solved it ugly, and cheated with the date in those rare cases,
using the next day, if the current was used, but I'd rather do
things cleanly.

Using the primary key of one table as part of the primary key for
another table just because there is a parent-child relationship
between those tables mean that we have a much tighter coupling
between the tables than we have with synthetic keys. While this
evil, tight coupling can sometimes be avoided in the *primary*
keys of dependent tables, we can't avoid having the entire
primary key (which contains business information for another table)
as foreign key fields in the dependent table.

Not only the data model is hampered by this. The classes in the
application logic, whether it's implemented in Python, Java or
C++ will carry along attributes that really belong to another
class! This also means that the cost of implementing a business
rule change is multiplied.

By using a uniform primary key type, we will also always have a
uniform foreign key type, and it will be much easier to change
the table structure. After all we sometimes realize the the X
objects aren't really atttibutes of the Y object, but rather of
the Z objects. Natural keys create some kind of software structure
cement, and makes changes in business rules very hard. Often,
these structures get cemented long before the product is even
launched. It's one thing that it's difficult to migrate loads of
important business data, but it *should* at least be swift to
change the system if it isn't running yet.

For a different problem that brought me to the same conclusion, see
http://www.thinkware.se/cgi-bin/thinki.cgi/CaseStudyUnbrokenSeries



--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program