Greetings everyone, I'm a noob here.
I could use some help designing part of a MySQL database I'm working on. This is an event database, so think of say a conference. I have sessions and every session has a session_type. A session_type might be a Keynote speech or a Workshop. Whatever kind of session it is, it can only have one session_type.
What's giving me heartburn is that each session_type has a different set of unique fields consisting of different data types associated with it. If you look at the image I posted, I included (3) tables prefixed with "session_type_" to illustrate this.
So I imagine the brute force way to approach this would be to append ALL of the possible session_type fields to my sessions table, as in a horizontal approach. Then add a session_type field and I'm done. That strikes me as lazy way to go about it, but please correct me if I'm wrong. If this is a valid and accepted approach, maybe I should go this route - it's certainly simple enough.
Alternatively, I could try to break out the session_types into separate tables, as I've started to do in the attached diagram. However, I'm uncertain of how to link to these tables. In my example I have a "session_type_id" field in sessions, but with (3) different session_type tables - how do you know what table that id is from? Do I need yet another field in sessions that's simply "session_type", which then tells my application code which table to query with the "session_type_id"? That strikes me as poor design too, redundant.
I guess I could have one field in the sessions table for each session_type, as in:
However, this also seems messy and redundant. If I had to query the sessions table to find all the workshops, I'd have to say something like:
SELECT * FROM sessions WHERE session_type_workshop_id IS NOT NULL
That seems hackish to me. Agreed? I'm also thinking a simple query like getting a count of distinct session_types from the sessions table would be rather challenging using this approach.
There must be a better way!
I'd appreciate any advice on how to go about cracking this nut. Ultimately the idea is to keep the basic sessions table very generic, with just the most basic information about ALL sessions in it - and then append the different unique parameters for the appropriate session_type to the sessions table.
I apologize for the lengthy post, but I'd rather provide a little too much detail rather than waste people's time with an misleading or incomplete request.
Thanks in advance.