[thelist] re: Tricky SQL

Tom Dell'Aringa pixelmech at yahoo.com
Wed Jun 19 10:30:08 CDT 2002


> 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

======================

I must be confused because I don't see where you need a union, you can get all the information you
need from a query to the intersection table (scores from multiple tables). Granted there will be
some JOINs but it shouldn't be anything hairy. Maybe I misunderstood the problem...

======================

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


=====
var me = tom.pixelmech.webDeveloper();
http://www.pixelmech.com/

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com



More information about the thelist mailing list