[thelist] SQL: database/table design

[Colin Gremillion]

Howdy all,

I've gone over this with my sorta-SQL-expert manager,
and he suggested what I've already implemented, but I
believe that there has to be a better way to do this.

So I have a database, with two relevant tables:
"seminar" and "speaker" where "seminar" contains the
information about various seminars and "speakers"
contains the information about various speakers. The
two are linked via SpeakerID, which is the primary key
in "speaker".
Search by speaker is no problem, and it's been working
fine. However, the people who will be using this have
requested a sort of secondary speakers field, and have
that be searchable as well.
My initial idea was to make one field in the "seminar"
table and separate each number (which would correspond
to a SpeakerID in the "speakers" table) with commas. I
do this a lot because I LOVE those split functions.
The problem is, how do you search it? How do you get a
wildcard to pull "1" but not "11"?
Anyway, my manager hated this idea, and suggested that
I make a new table, where each event-speaker
relationship would have a record. So I now have this
table with three fields: "SpeakerID" (linked to
"speakers"), "EventID" (linked to "seminar") and
"SpeakerType" which says if they're "primary" or
"secondary".
I've got the statement working, but it's a nested
inner join and I hate doing those. Is there a better
way to do this? Keep in mind that events and speakers
are always mutually exclusive.
Any help would be appreciated. I know this is
complicated (but is SQL ever not?), so feel free to
email me directly with questions and solutions.

Thanks,
colin


	
		
__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/