Michael Bayer skrev:
> Johan Hahn wrote: > > Say I was about to make a telephone book database. I > > would need four columns: id, first name, last name, and > > phone number. > > > > Table('telephone_book', metadata, > > Column('id', Integer, primary_key=True), > > Column('first', Unicode(100)), > > Column('last', Unicode(100)), > > Column('phonenumber', String(11)) > > ) > > > > Phone numbers are fairly unique, not many rows in the > > table will have the same value. But first names are often > > the same. As are last names. Although the set of first > > names and the set of last names are fairly disjoint. Hence, > > it would make sense to break out the two name columns > > into two separate tables to save space. > > how much space exactly would you hope to save via such a structure ? > consider that these additional tables will require new indexes of their > own, plus indexes back to the primary table...its not clear if youre > saving very much at all. the space savings afforded if you have a > truly large table of usernames would also have to be weighed against > overhead of querying across three tables instead of one as well as > inserting into three tables with three sets of indexes instead of > one...and if you have a relatively small table (say, under a million > rows), your space savings would be fairly trivial by today's > standards....this smells like "premature optimization" to me (or > excessive normalization). yes.. thanks for pointing it out.. and sorry for not mentioning that this was a toy example.. in my real example I will have maybe 150 million rows with a bit longer texts that are expected to show even more "sameness" than names > > My question is: how can you do this as transparently as > > possible with sqlalchemy? I was thinking along the lines > > of a new type called XUnicode (or something). The user > > expected behaviour of the two tables (when mapped) > > would be exactly the same only database would be more > > normalized in the latter case. > > > > Table('telephone_book', metadata, > > Column('id', Integer, primary_key=True), > > Column('first', XUnicode(100, tablename='first')), > > Column('last', XUnicode(100, tablename='last')), > > Column('phonenumber', String(11)) > > ) > > well, creating columns that magically act like tables would be pretty > "out there" to say the least. if you need to have multiple tables look > like one, then you work with joins. easiest would be to implement the > join behind a view in your database. or, SQLAlchemy can provide > similar functionality by creating a selectable which you can treat > mostly like a table: > > telephone_book = telephone_basic. > join(firstnames, > firstnames.c.id==telephone_basic.fname_id). > join(lastnames, > lastnames.c.id==telephone_basic.lname_id).select(use_labels=True) thank you michael! I'll try this solution to see how it turns out before thinking of "out there" stuff again! :) ...johahn --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---