Event database design

[rocco123123]

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:

session_type_keynote_id
session_type_workshop_id
session_type_panel_id

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.

[BitBlt]

Excellent questions you have. This very scenario is one of the reasons to separate logical design from physical design. What you might find is that if you complete a logical model there could be a better way to segregate your physical data than by just following your logical model. I know this sounds like a cop-out, lots of "might" and "could" but without knowing more about your scenario, I can only give very general guidance.

Now, it's highly likely that the project you're working on is just a few users for a small company. It may not be worth the time and effort to go through all these steps. If so, just make a flat table and you're done. Pretty? No, but it will get your project finished quickly and perform adequately.

If the project is for thousands of users across multiple time zones and pumps through millions or billions of transactions, it probably IS worth the effort. A few hours or weeks of analysis will save tens of thousands of hours of development and support.

The first step is to start with a parent entity Session. You said before that each session can only have one Session Type. Fine, give SessionType as an attribute of Session. Each of the Session Types can be characterized with the "is a" relationship...e.g. a Session Type Workshop IS A Session...a Session Type Keynote IS A Session. This tell you that each of these child entities has SessionId as it's primary key as well (keep in mind, we're still logical here, not physical).

When you are satisfied that you have captured all the interesting attributes for each sub-type, , look for the things that are common between the sub-types, and the things that are specific to each sub-type. It may be that there's more commonality than you think. If there are attributes that are common among ALL sub-types, they belong in the parent entity. If they are specific, they belong in the appropriate child. Then, if there are lots of attributes that are common among one or more sub-types but NOT all, that may indicate that some sub-types can be combined. Or, you might be able to take those common attributes and segregate them out into a separate child entity of Session. There are lots of possibilities.

When it's time to physicalize, you then have the choice to just physicalize to mirror the logical model, to roll the sub-type attributes into the parent, or some combination. Again, without knowing more about what attributes you want to capture, it is hard to provide much guidance. If you want a more thorough explanation of how to go about all this, I strongly recommend you visit the IDEF website www.idef.com and do some research.

Sorry if this wasn't helpful, but good luck anyhow!