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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
