Event Listings Data Model

Question

I'm working on an app which I will be displaying events (sporting events, concerts, etc, etc). I'm trying to come up with a model where I can single out teams playing for sporting events, and bands/artists playing in a concert.

My initial stab at is it to have an events table, team table, band/artist table. But I can't figure out the optimal way of handling the two teams or the many band/artists performing at an event.

Is it ok to have multiple fields NULL if they do not apply to the record? I always thought it's best to limit the amount of NULL fields in a record.

Answer 1

Lots of ways to attack this.

You'll certainly have an events table.

Whether you split sports teams and bands into separate tables will depend on what information you're maintaining about each and how you feel about the NULLS. My understanding is that there is virtually no performance hit by having nulls in your records (I'm sure it varies by database) -- the real downside is potentially an unwieldy table if you have a lot of sports- and music-related fields all mixed in together.

If you do create separate sports and concert tables, you'll need to consider how you'll handle the "etc, etc" from your question. Into which table will magicians and sword-swallowers go?

As for connecting multiple teams/acts with an event, I'd be inclined to create an intermediate table to perform that linking. Each record would reference an event id and a performer id. You could have one record for an event (one performer) or two records for a sporting event (two teams) or n records for a many-act concert.

Then to pull up the show bill, you'll look up your event, and then use the intermediate table to find all the acts appearing at that event, and use those ids to get the name and details for each act.