Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Would appreciate feedback on design

    I'm working on an Event management database in MySQL. All my Events share some common fields, but after that each "event_type" will have a number of fields unique to that type.

    My inclination is to create a separate table for each event_type, where the unique detail for that type of event will reside. Basically I believe this is what you'd describe as vertical partitioning the data.

    One alternative would be to add ALL of the possible columns to the "events" table, and make them nullable. This seems like a waste of space, but would certainly reduce the likelihood of running into the need for some wacky joins down the road.

    In terms of feedback, I'd appreciate any insights on pros and cons to these approaches. Is there yet another alternative design that would be considered superior to both of these?

    Thanks in advance.

    Chris Baker

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I think you are on the right track with your self styled 'vertical partioning' of data, have a google for sub/super types.

    the sub super type paradigm shouldn't enforce 'whacky' joins at a later date.

    there is a thrid way the dreaded OTLT / EAV model, but we don't talk about that model here, unless we are disparaging it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    I'm back... I did some reading on the subject sub/super types, but I'm still unclear on how to best approach this. I've attached an image to help illustrate what I'm working with.

    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.
    Attached Thumbnails Attached Thumbnails db.png  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    suspect the name / subject / something are irrelevant, they can be stored in sessions.session_short_title

    a suggestion dont' repeat the tabel name in the tbales columns
    eg
    instread of session_name in sessions use just name. why? well it tends to make reading the column names in a query harder to understand

    eg
    select sessions.name, sessions.short_title,sessions.Type_id, garbage.blah, garbage.di, garbage.blah! from sessions......
    compared to your appraoch of
    select sessions.session_name, sessions.session_short_title, sessions.session_Type_id, garbage.blah, garbage.di, garbage.blah! from sessions......

    attempting to store the ID of the subtype in the session table is a non starter

    presuming that the sessions.type_id identifis the type of session then your count is simple enough
    select count(is) as NoSessions from sessions
    group by sessions.events_id, sessions.type_id

    Im highly suspicious of the sessions.tags entity, if that is what i think it is (say a list of comma separated words) then this column should be pushed into a table of its own with one word / phrase per row
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Thanks for the feedback.

    Quote Originally Posted by healdem View Post
    attempting to store the ID of the subtype in the session table is a non starter
    This is basically what I'm asking for help with... If I have (3) different session_types and I describe them in (3) different tables, what is the best way to store session_type.id in sessions? Because it could be session_type_workshop.id or it might be session_type_panel.id - depending on the type of session.

    If I had just (2) tables: sessions and session_types, then it's simple. I store session_types.id in a field called sessions.session_type_id and I'm done.

    However, I have (3) or more session_types ... I'm not sure how to make a relation that will be easy to manage.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •