Re: [SQLObject] two postgres questions
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Bud P. B. <bu...@si...> - 2003-06-02 10:11:06
|
Magnus, I very much agree with your approach to primary keys. This is why I suggested to use GUID or UUID in SQLObject some time ago and voted against the possibility to use multi-column primary (and foreign) keys. In my prototype ORM, I use a guid implementation that I found in an ASPN cookbook and it seems to work just fine. I believe I was originally convinced of this by a paper by Scott Ambler--but I didn't find the refernece anymore.. -b On Thu, 29 May 2003 03:56:30 +0200 Magnus Lyckå <ma...@th...> wrote: > 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 > > > > ------------------------------------------------------- > This SF.net email is sponsored by: eBay > Get office equipment for less on eBay! > http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5 > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |