NYCPHP Meetup

[Tim Gales]

You probably have already thought of this, but if things
slow down too much, you could put the judge's name in the
event table. This brings about the usual headaches
(referential integrity checks you have to make) when you
de-normalize a design. But it might not be too bad (I am
assuming the judges don't change much on the events). 

Just a thought

T. Gales & Associates
Helping People Connect with Technology
http://www.tgaconnect.com

-----Original Message-----
From: talk-bounces at lists.nyphp.org
[mailto:talk-bounces at lists.nyphp.org] On Behalf Of David
Mintz
Sent: Thursday, September 18, 2003 3:34 PM
To: talk at lists.nyphp.org
Subject: [nycphp-talk] more queries, or bigger in-memory data
structure ?


I'm gonna try to explain this as briefly and clearly as I
can.

I've got a scheduling program for us court interpreters
where there's a
table called 'events'. An event has attributes like date,
time, judge,
type of proceeding, language, etc. Of course things like
judge and
language are stored as smallints pointing to related records
in other
tables. Thus, in order to fetch human-meaningful data I have
to join
several tables.

"SELECT events.event_id, events.docket,events.event_time,
 	judges.lastname, proceedings.type, languages.name,
	events.notes, languages.lang_id,
	FROM judges, proceedings, languages, events
	WHERE events.event_date = '$date'
	AND events.judge_id = judges.judge_id
	AND events.language_id = languages.lang_id
	AND events.proceeding_id =
proceedings.proceeding_id"

OK, so far so good. But I also want to display data from
tables that are
in a 1-M relationship to 'events'. An event can have zero or
more
interpreters assigned to it, or zero or more defendant names
associated
with it. And I only want to display row of data per event.
So even if I
could figure out the LEFT JOIN syntax to left-join multiple
tables in this
way, if such is possible, it still wouldn't give me what I
want.

What I've been doing is building one associative array with
the event_ids
as keys and merging it all together with three queries.
(There are rarely
more than about 30 elements at this point.)

My question is, would it be more efficient to do the first
query first,
and as I loop through the result, query the related tables,
resulting in
maybe 60 queries per page access? Or do it with a lot fewer
database
calls, but suck up more memory?

I've always thought it kind of rude to hammer the database
when there's
another way, but frankly I'm not sure, maybe that's what
MySQL is there
for.

TIA,

---
David Mintz
http://davidmintz.org/
Email: See http://dmintzweb.com/whitelist.php first!

"Y dále p'abajo"

	Tito Rojas
_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk