How to model Event Database

[doubledee]

I am trying to build the back-end for and Events Registration system I've been working on.  (It appears more complicated than I originally thought?!)

 

Right now I have the following tables...

 

- Show (e.g. Flower Show)

- Venue (e.g. Ramada Inn)

- Event (e.g. Oct 1, 2011 9:00am - 12:00pm)

 

 

And these relationships...

 

One SHOW can have many VENUEs

One VENUE can have many SHOWs

 

More precisely...

 

One SHOW has zero to many EVENTs

One VENUE has one to more EVENTs

 

 

So I guess EVENT is a "junction table" for my many-to-many (SHOW-to-VENUE).

 

Here is one place where I am getting confused...

 

The PK for EVENT would need to be...

 

- ShowID

- VenueID

- Date/Time

 

...to be unique.

 

 

Where does the Date/Time come from?

 

Do I just insert it into EVENT or do I need a DATETIME table?

 

 

Here is why you'd need DateTime...

 

Flow Show at Ramada Inn (Oct 1, 2011 9:00am - 12:00pm)

Flow Show at Ramada Inn (Oct 1, 2011 1:00pm - 4:00pm)

 

 

I have lots of other questions but this is a start...

 

 

Debbie

 

 

[Muddy_Funster]

For the date time issue I persoanly would have 4 fields for that: event_date, event_stat_time, event_end_time, am_pm_flag.

 

By including additional ID P/K Fields in all three databases, with columns for relational table id fields in the tables that need them you make things much easier to refference and maintain.

 

Just my thoughts on it.

[doubledee]

For the date time issue I persoanly would have 4 fields for that: event_date, event_stat_time, event_end_time, am_pm_flag.

 

By including additional ID P/K Fields in all three databases, with columns for relational table id fields in the tables that need them you make things much easier to refference and maintain.

 

Just my thoughts on it.

 

That doesn't answer my question...

 

All I need is a way to uniquely identify a particular EVENT which is a SHOW occurring at a VENUE at a particular place in TIME.

 

My question was about how to model things...

 

 

Debbie

 

[gizmola]

This is what I'd typically do given your outline.  Obviously you'd have to fill out the tables, but the barebones of it I would do like this.  This does not utilize "defining" relationships between the tables that join to events, which simplifies adding events to the events table, and also leaves duplicate checking to you, but as this needs to deal with start/end times at a venue anyway, it handles it from the database standpoint efficiently.  I also included the constraints but unless you use the innodb engine with mysql (the default is myisam) constraints are dropped so they won't have any effect on the structure or functionality.

 

 

 

# ---------------------------------------------------------------------- #
# Tables                                                                 #
# ---------------------------------------------------------------------- #

# ---------------------------------------------------------------------- #
# Add table "`show`"                                                     #
# ---------------------------------------------------------------------- #

CREATE TABLE `show` (
    show_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40),
    CONSTRAINT PK_show PRIMARY KEY (show_id)
);

# ---------------------------------------------------------------------- #
# Add table "venue"                                                      #
# ---------------------------------------------------------------------- #

CREATE TABLE venue (
    venue_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40),
    address VARCHAR(40),
    CONSTRAINT PK_venue PRIMARY KEY (venue_id)
);

# ---------------------------------------------------------------------- #
# Add table "event"                                                      #
# ---------------------------------------------------------------------- #

CREATE TABLE event (
    event_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    show_id INTEGER UNSIGNED,
    venue_id INTEGER UNSIGNED,
    startDate DATETIME,
    endDate DATETIME,
    CONSTRAINT PK_event PRIMARY KEY (event_id)
);

# ---------------------------------------------------------------------- #
# Foreign key constraints                                                #
# ---------------------------------------------------------------------- #

ALTER TABLE event ADD CONSTRAINT `show`_event 
    FOREIGN KEY (show_id) REFERENCES `show` (show_id);

ALTER TABLE event ADD CONSTRAINT venue_event 
    FOREIGN KEY (venue_id) REFERENCES venue (venue_id);

[doubledee]

Ewww.. and ERD!!!! 

 

(How did you make that?!)

 

A few items...

 

1.) What if I renamed my tables like such...

 

- EVENT (vs "Show")

- EVENT_DATE (vs "Event")

- VENUE

 

 

2.) In the Junction Table, why not use "Start_DateTime" as the 3rd Key to make a Composite Key?

 

Using "Event_ID" is derived and represents no real-world thing which most would say is bad...

 

Thanks,

 

 

Debbie

 

[gizmola]

I used a tool called Dezign for databases to make the diagram.

 

If you find the DDL useful, you can edit it to change the names to be whatever works best for you.

 

Whether or not you use an event_id or not is really a pragmatic decision. Keys are needed to guarantee uniqueness.  That is the first and most important thing. Whether or not you should use a defining relationship that results in a concatenated key or not depends on a lot of different things.  Having done these types of applications a lot I can tell you that getting a key collision on Start_DateTime is not that valuable.  What is important is that your application takes into account both start and end ranges and insures for a single venue that you are not going to overlap.

 

Consider:

 

Start_DateTime 1:00pm

Start_DateTime 1:00:01 pm.

 

No key problem there, but your application most certainly doesn't want to allow someone to add a 1:00:01 row be added.  So you are going to have procedural code that determines where there are holes in  a schedule, requring start+ end regardless. 

 

I'd suggest given that situation that you opt for keeping the nuts and bolts of generating a key simple. The other reason to do so is that if you end up adding related tables to an event row, you'll save a lot of space not having to repeat the entire key in order to make that relationship.

 

The other practical matter is that you'lll be most likely be doing queries by venue and queries by show.  Thus you will need indexes that cover your queries.  One single index will probably not be good enough for everything you need. 

 

With that said, you can certainly opt for the dependent relationships and have those be a concatenated key. It won't stop you from inserting rows that are injected into the time holes improperly, and your code will need to be entirely different than the code you use to insert the rows into the auto_increment columns.  You will need more complicated logic for the event table in either case, so my advice is to avoid the complexity of the concatenated key, but either approach is valid and frequently used.