db design question for php and mysqs

[Anon]

I'm trying to build a php script with similar functionality to the event calender at http://www.peakei.com/bpevents/calendar.php3
You would basically have multiple industries, segments, and event dates for one event. You also have a search form where you can filter the event calender by Region, Industry, and the Indstury's segment.

The problem is constructing a web page (using php4) with this list of events (maybe like 10 or so) with all of this information (each event will be listed in its own section in a table row with all relvent info). The information would be spread across 4 tables using basic normalization principals (see url for an example calender). I want to minimize the number of queries I make to the database and keep the php script from eating up too much memory and cpu power. I am using a shared MySQL connection. I was wondering which trade offs make sense:

Here is a simplified database schema for the first design:

event_table: eventid, eventtitle, region, primary key(eventid), index(eventtitle)
industries_table: eventid, industryname, primary key(eventid, industryname)
segment_table: eventid, segment, primary key(eventid, segment)
eventdates_table: eventid, eventdate, primary key(eventid, eventdate)

Now this is the most ideal database design I can think of but is going to require at least 4 or 5 sql queries just to construct the calender per hit. These queries can also involve nested queries since the calender can be be sorted by event date by request (and before I can sort by event I have to find the earliest event date for each event. Otherwise working with this database schema is quite easy.

Then there is this table:

event_table: eventid, eventtitle, region, industries, segments, event_start_date, other_event_dates, primary key(eventid), index(eventtitle), index(event_start_date)

The industries, segments, and other_event_dates are all sets which contain all the infomation needed (perhaps by utilizing the SET type...or maybe using a list of items seperated by a weird symbol). Its quite easy to search for what I need using out of that also. The only real advantages are that I can just get all the information in one query, php coding is more straightforward, and sorting by event dates is very easy. I will loose indexing power maybe (can SET types be indexed?) and have a harder database to make upgrades to. Also I will have to be carefull if new event dates are added and make sure the earliest event is in the event_start_date field.

So out of these possiblities which would put less server load and what would be the value of having what could be considered bad database design? Thanks for your time for reading this and your responses!

RA

[philsown]

You're on the right track with separate tables. If things like Segment are a list that doesn't change much, I would keep them in a separate table. But then take that info and basically map it back into one table... like this:

create table segments (
seg_id int not null primary key auto_increment
, segment varchar(50)
, unique index (segment) -- why have 2 that match?
);

create table events (
event_id int not null primary key auto_increment
, title varchar(100)
, description blob
, region varchar(100)
, date_start date
, date_end date
, seg_id int references segments(seg_id)
);

Note the seg_id column in the events table.

When I want the calendar I say:

select * from events order by date_start;

if I want to filter by segment I say:

select e.* from events e, segments s
where e.seg_id = s.seg_id
and s.segment = 'Some Segment'
order by date_start

This same logic can be applied to all the other tables.