Hi Michael, Thanks for taking the time to provide this informative and helpful reply, it's much appreciated.
Cheers, Paul On Mon, Sep 7, 2009 at 4:08 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Sep 7, 2009, at 3:27 PM, Paul Nesbit wrote: > > Hi, > > I'm in the process of setting up SQLAlchemy classes after having defined my > database tables. I'm at the point where I'm mapping relationships, and > I've realized now that I may have designed my tables in a sub-optimal way, > or in a way that may not lend itself well to mapping relationships with > SQLAlchemy. Before asking any questions about how to map relationships in > my model, I'd like ask a more general question about database design. > > I don't know how to describe the design I've chosen, other than to say I've > seen it used in other data models (WorldPress and RT) in which relationships > are defined in "meta" tables with key value pairs, rather than one-to-many > relationships between the entity tables and a designated relaionship table. > Here's an example of the two designs: > > *Users > id Name email > *1 bob b...@company > 2 nancy na...@company > > *Roles > id role description* > 1 administrator access to all > 2 editor can change doc content > 3 publisher can create/move/delete docs > > The "meta" table design: > > *UserMeta > id user_id metakey value* > 1 1 role_id 1 > 2 2 role_id 2 > 3 2 role_id 3 > > The one-to-many design: > > *UserRoles > id user_id role_id* > 1 1 1 > 2 2 2 > 3 2 3 > > I prefer the meta table design because a) I'm seeing clueful application > developers use it (not a great reason, I know) and b) it means fewer tables > (e.g. if I create a company table, I don't need to also create a > "usercompanies" table). I suspect there's other benefits, however I'm not > aware enough to know them. > > Can anyone here speak to the benefits of one model over the other? > > If I proceeded with the meta-table option, can I expect mapping the > relationships between the meta-table's key/value relationships to be > painful, or not possible? > > > SQLA can accommodate both versions, but the "meta" is more difficult to > model, since it ultimately involves modeling any number of relations between > any number of different mapped classes among one giant association table, > and the conditions by which two remote tables are joined together is more > complex, less performant, less safe and less flexible from a relational > database point of view. > > I located an old email I wrote to some colleagues about this subject but > its perhaps a little too specific to what we were doing then. The basic > answer is that theres no advantage whatsoever to having fewer tables, and > there are many disadvantages to using a model that batches lots of disparate > data into one huge table and disallows proper constraints. More tables > allow data to be partitioned among fewer sets of rows. Therefore lookups > and joins are less costly for all relationships, the cost of one or two > enormous relationships isn't passed onto all the other relationships in the > database, and the distinct tables of relationship data are generally more > easily managed and partitioned across disks and servers. Allowing > individual columns to represent exactly one relationship elsewhere allows > efficient indexing (i.e. indexing of a single integer field, instead of a > composite across the id plus metakey), greatly reduces the space required to > store the relationships (no need to store "metakey"), and allows the > construction of proper foreign key constraints. Having to "keep track" of > many association tables, the only issue I can think of that might be raised, > is not an issue since you're using a SQL/ORM toolkit to transform that into > an invisible detail. > > The "meta" model you have would require that "value" not have any proper > constraints which for any serious database designer is a total dealbreaker - > any number of rows can be inserted into UserMeta, or created via deletions > of related objects, that are entirely invalid and your database has no way > to prevent such corrupted data from being created, nor is there any > capability to CASCADE deletes from remote objects. The reason such models > exist (primarily in the PHP and Ruby on Rails worlds) is because they > ultimately derive from MySQL and its famous culture of "who needs foreign > keys?". While MySQL devs have recanted that philosophy years ago, the awful > habits they've spawned among these communities persist. > > i wrote a blog post about something slightly more complex than this called > "polymorphic association", which illustrates Rails' incorrect methodology of > implementing this (and how to map the identical thing in SQLAlchemy) and a > better, constraint-bearing method at http://techspot.zzzeek.org/?p=13 ... > but that's just a related nugget. The use case you have here only need use > whats described in the SQLAlchemy docs at > http://www.sqlalchemy.org/docs/05/mappers.html#many-to-many . You also > don't need "UserRoles.id" since "user_id/role_id" forms a composite primary > key for that table. > > > > > > > -- p...@nesbit.net | 416.642.6920 | paul.nesbit (GTalk/MSN) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
