[thelist] Tricky SQL

[rudy]

> Intersection table: (games)
> gamesID
> Score
> Date
> OriginID (foreign key for inserting table (ie. mensID))
>
> Make sense?


hi tom

yes, it does, but i don't see that it buys anything, and in fact it
introduces redundancy which takes up additional space (ken said he had
large tables already) and is harder to maintain

you still have to do a UNION to get results from both mens and womens
tables -- the only difference is, this time there's an extra JOIN thrown
into the mix, and if the mens and womens tables are properly indexed, the
filtering will be done on them anyway and not on the extra table

but your idea is a good one, and here's a good example of how it would be
used

consider a cms which has very large articles

each article has several identifying fields, like title, url, author, date
posted, date revised, category, hitcount, and rating

each article also has a large text or memo field to hold the guts of the
article, which could run to several k

it makes sense to split this table in half, such that the memo field is in
a table by itself, with the same pk as the other table

this is one-to-one relationship that makes for some excellent performance
gains when all you want is to search or list just the identifying fields --
for example, to display a list of article titles, authors, dates, and links
to a page where the guts will be displayed

notice there is no redundancy (other than the bare minimum, a pk in the
second table)

make sense?


rudy