Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24

    Need help modeling Events Registration system

    Hello. This is my first post.

    Have been in IT for a number of years, but am learning web development in my free time.

    I have a couple of potential clients that need web-based solutions, and one that I'm currently working on is a web-based system that lets people register for Events in the region. (e.g. Flower Show, Gun Show, Car Show, Renaissance Festival)

    While my front-end registration process is fairly simple, I am starting to see that the back-end data is more involved than one might think?! So here I am needing help!!

    Details to follow in next post...

    Sincerely,



    Debbie
    aka "Double Dee"

  2. #2
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Here is a list of candidate Tables...

    - Show
    - Venue
    - Event
    - Room
    - Ticket
    - Order
    - Customer


    Maybe it makes sense to just tackle one portion of the larger problem first?


    Potential Relationships...

    - One SHOW can have many VENUEs
    - One VENUE can have many SHOWs

    An "EVENT" is when a SHOW and a VENUE come together on a certain Date and Time.


    Here is a quick stab at my Tables...
    Code:
    SHOW
    ---------
    - id
    - brief_description
    - detailed_description
    - speaker
    - length (hours)

    Code:
    VENUE
    ---------
    - id
    - name
    - address
    - picture
    - map

    Code:
    EVENT
    ---------
    - show_id
    - venue_id
    - datetime_of_show  This is one area I need help?!
    - room_id
    - tickets_sold
    - tickets_redeemed

    I'm not sure if I need a DATETIME table or what?

    For example, you could have...

    SHOWS
    - Scrapbook Show
    - Coin Collecting How-To


    VENUE
    - Holiday Inn
    - Hampton Suites


    EVENT
    - Scrapbook Show at Holiday Inn on Oct 1, 2011
    - Scrapbook Show at Holiday Inn on Oct 2, 2011

    or

    - Coin Collecting How-To at Hampton Suites on Oct 5, 2011 from 9:00-12:00
    - Coin Collecting How-To at Hampton Suites on Oct 5, 2011 from 1:00-4:00



    Debbie

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by doubledee View Post
    I'm not sure if I need a DATETIME table or what?
    no, you don't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You need to figure out how you want to think about date and time to answer that question. If all you need to do is isoltate a split second in time (such as the show opening), then most database products can do that nicely in a single attribute (a column in SQL).

    Most of your events are probably more than one block of time. Some time for venue setup (when only vendors are allowed), some time for the show proper (which may be multiple blocks of time spread over multiple days), and some time for venue tear down. If this is the case, then you need to decide how you want to store and manage these time blocks.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by Pat Phelan View Post
    You need to figure out how you want to think about date and time to answer that question. If all you need to do is isoltate a split second in time (such as the show opening), then most database products can do that nicely in a single attribute (a column in SQL).

    Most of your events are probably more than one block of time. Some time for venue setup (when only vendors are allowed), some time for the show proper (which may be multiple blocks of time spread over multiple days), and some time for venue tear down. If this is the case, then you need to decide how you want to store and manage these time blocks.

    -PatP
    Whoa. You lost me there. (Or maybe I lost you!)

    Here is some sample data...

    SHOW:
    -----------
    id = 1
    name = Flower Show
    description = "Come see all sorts of exotic flowers."

    id = 2
    name = Model Airplane Show
    description = "See all types of model airplanes..."


    VENUE:
    -----------
    id = 1
    name = "Holiday Inn"
    address = "1350 Walnut Avenue, Sioux Falls, SD"

    id = 2
    name = "Hampton Inn & Suites"
    address = "2700 Airport Road, Minneapolis, MN"


    EVENT:
    ----------
    Flower Show at Holiday Inn (on Sat, Oct 15 from 9:00am-12:00pm)
    Flower Show at Holiday Inn (on Sat, Oct 15 from 1:00pm-4:00pm)


    In order to be able to distinguish several instances of the same "Show" at the same "Venue" it seems like I need to add a Time element.

    And maybe that should go in the "EVENT" table?

    Or maybe I just insert WHEN the particular Event happens another way?

    I'm not sure what is the best way to do that.

    Or do I need to structure my tables differently?


    Debbie

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to add the event's date and time (possibly multiple columns, e.g. from_datetime and to_datetime) to the events table

    it doesn't require a datetime table, just add the data to the events row

    the PK of the events table should be a 3-part composite key: the show, the venue, the date/datetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by r937 View Post
    you need to add the event's date and time (possibly multiple columns, e.g. from_datetime and to_datetime) to the events table

    it doesn't require a datetime table, just add the data to the events row

    the PK of the events table should be a 3-part composite key: the show, the venue, the date/datetime
    While I may want to store the Start_Time and End_Time, since a SHOW would never run concurrently against the same SHOW, I assume I can just pick some Date/Time combination like "Start_DateTime", right?

    (BTW, can you think of a better/prettier field name for that if that is okay?!)


    Debbie

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start_datetime is a fine name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    whatever names you use for tables, columns should have a clear an unambiguous meaning. many organisations have a formal naming convention.
    whether you call your column start_datetime, Start_DateTime, startdatetime, StartDateTime doesn't really matter.

    personally I'd try to keep the names as short as possible and try to avoid referring to the datatype in the name. there's good arguments for using different naming conventions for Code such as "Hungarian"
    Hungarian notation - Wikipedia, the free encyclopedia
    however you should not use Hungarian in table / column design and modern advice is not to use "Hungarian" at all
    General Naming Conventions

    FWIW I tend to use StartsAt and EndsAt for such columns, but that's my prejudice. go with what works for you
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    So you don't need to deal with "multiple segment" events like a flea market? As an exmaple those might have:

    Code:
    07:00 to 15:00  2020-01-15  vendor setup
    16:00 to 21:00  2020-01-15  Opening gala
    08:00 to 17:00  2020-01-16  day 1
    08:00 to 15:00  2020-01-17  Day 2
    15:30 to 18:00  2020-01-17  Vendor tear down
    07:00 to 12:00  2020-01-18  Staff clean up
    This kind of event requires a whole different way of thinking about time.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's harsh, man

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Time is too slow for those who wait, time is too swift for those who fear, time is too long for those who grieve, too short for those who rejoice, but for those who love – time is eternity."

    Henry van Dyke

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by Pat Phelan View Post
    So you don't need to deal with "multiple segment" events like a flea market? As an exmaple those might have:

    Code:
    07:00 to 15:00  2020-01-15  vendor setup
    16:00 to 21:00  2020-01-15  Opening gala
    08:00 to 17:00  2020-01-16  day 1
    08:00 to 15:00  2020-01-17  Day 2
    15:30 to 18:00  2020-01-17  Vendor tear down
    07:00 to 12:00  2020-01-18  Staff clean up
    This kind of event requires a whole different way of thinking about time.

    -PatP
    I'm still not following you.

    My goal is to set up my database so I can REGISTER people for an Event - not manage the Event.

    Maybe there is a concert like "Sioux Falls Jazz Concert" or an entertainer like "Miranda Lambert in Concert" then I need a way to logically keep track of who/what the SHOW is, and the VENUE it is at, and the actual TIME it occurs, because of the many-to-many relationships.


    *IF* I had a two-day Flwa Market, then I would still just need some Date/Time to differentiate it from the next Date/Time. (Once you buy a ticket you are in, right?)

    What can't happen is two records...

    "Learn How To Start an Alpaca Farm" at Holiday Inn on Sat, Oct 1, 2011

    "Learn How To Start an Alpaca Farm" at Holiday Inn on Sat, Oct 1, 2011


    with no way to distinguish the Morning Event from the Afternoon Event...


    Debbie
    Last edited by doubledee; 10-07-11 at 00:08.

  14. #14
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    Quote Originally Posted by r937 View Post
    that's harsh, man

    What is harsh??


    Debbie

  15. #15
    Join Date
    Oct 2011
    Location
    Arizona
    Posts
    24
    I am wondering if this naming convention would be better for Tables...

    - EVENT (vs Show)
    - EVENT_DATE (vs Event)
    - VENUE

    Or is it bad to have a junction table that is a derived name of its parent table?


    Debbie

Posting Permissions

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