Need help modeling Events Registration system

doubledee · #1 (**permalink**) 10-02-11, 16:21

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"

doubledee · #2 (**permalink**) 10-02-11, 16:31

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

r937 · #3 (**permalink**) 10-03-11, 05:55

Quote:

Originally Posted by doubledee

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

no, you don't

Pat Phelan · #4 (**permalink**) 10-03-11, 11:44

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

doubledee · #5 (**permalink**) 10-05-11, 23:15

Quote:

Originally Posted by Pat Phelan

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

r937 · #6 (**permalink**) 10-05-11, 23:25

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

doubledee · #7 (**permalink**) 10-05-11, 23:38

Quote:

Originally Posted by r937

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

r937 · #8 (**permalink**) 10-06-11, 00:41

start_datetime is a fine name

healdem · #9 (**permalink**) 10-06-11, 02:54

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

Pat Phelan · #10 (**permalink**) 10-06-11, 12:30

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

r937 · #11 (**permalink**) 10-06-11, 12:36

that's harsh, man

r937 · #12 (**permalink**) 10-06-11, 12:38

"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

doubledee · #13 (**permalink**) 10-06-11, 23:04

Quote:

Originally Posted by Pat Phelan

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

doubledee · #14 (**permalink**) 10-06-11, 23:05

Quote:

Originally Posted by r937

that's harsh, man

What is harsh??

Debbie

doubledee · #15 (**permalink**) 10-06-11, 23:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech