2

Description

I am building a database for storing social events. They can be one-time and recurring. The price and capacity can differ on per-date basis. The events have variants that can also alter the base price. Not all variants are available every date, thus dates are per-variant.

Tables

I was thinking of having the following structure (fields omitted for simplicity):

event  event_variant  event_date  event_recurrence  event_config
-----  -------------  ----------  ----------------  ------------
id     id             variant_id  variant_id        id
name   event_id       config_id   config_id         event_id
       plus_price     date        rrule             price
                                                    capacity

The recurring events would then be generated in a materialized view. If date and recurrence overlap, date would be preferred.

Event

The event table holds basic information important for searching, like the location, description, event category.

Event variant

A specific variant of an event. Each event has 1-n event variants. Event variants have their title and description, as well as additional price information.

Event date and recurrence

Recurrent dates are ones that are known to happen beforehand. Dates are for irregular events. Each irregular and recurring date has a variant assigned. Some variants may not be available on certain dates. Each date also has a config assigned, with price and capacity for the specific date/recurrence.

Event config

Holds information about the max capacity (current cap. calculated based on purchases) and the price of the current event.

Example

Event named escape room. Has a description, images, etc.

Variants

  • Basic - no extra cost, lasts 1h
  • Extended - more sub-rooms unlocked, lasts 2h. Costs 5$ extra

Dates

Every tuesday and friday at 18:00 (stored in event_recurrence), with special dates for some holidays (stored in event_date).

Prices

  • 20$ during summer holidays, 15$ the rest of the year (recurring dates). Certain dates can cost extra (christmas, easter...).

The problem

Basically this rustles my jimmies in two different ways:

  1. Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.
  2. Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?
  • It would help a bit if you'd explain the meaning (with a few examples) of the information of the different tables. Your introduction and the naming suggests some things, but not enough. – joanolo Mar 1 '17 at 8:01
  • @joanolo added a brief description for the tables and a use case example – Idefixx Mar 1 '17 at 9:44
  • I would be inclined to think that, at some point, a recurring event should not be overridden by a dated event (for instance, if a recurring event is scheduled for less than 2 hours from now, and there's a special request for the same time, the dated event should not override the recurring event. If so, I'd recommend that an entry for a recurring event should be added into the event_date table once they reach the "lock date". If so, then it probably makes sense for the config table to go away, or to only apply to the recurring event table. <continued> – RDFozz Mar 1 '17 at 18:04
  • If the DB is to retain any sense of history, and if configurations change over time, then you'd want the table with the actual events that have or will occur to reflect the configuration information that applied at the time. Example: If the price for an event goes up by $5 in the configuration, you wouldn't want to show the price for last year's occurrences of the event as being $5 higher as well. – RDFozz Mar 1 '17 at 18:06
0

Your questions, with my responses:

  1. Does event_config need a reference to event via event_id? It makes sense to know to which event the config belongs to, but at the same time it feels redundant since it's implicit from the relationship with dates.

Adding the event_id to the event_config table allows you to easily design a user-interface for modifying the event_config table, without needing a complex reference to an actual event instance. This might be useful for "default" configs.

This also allows an easier code path for presenting data to the user prior to the user choosing a date.

  1. Isn't event_config an unneeded complexity? Its sole purpose is to provide shared fields to event_date and event_recurrence. Wouldn't it be better just to have the price and capacity columns both in date and recurrence?

event_config looks like a good optimization to me. I see it as a way to manage configurations independently of specific events in time. For instance, you might have a "holiday" config, a config for each weekday, and a config for "saturday" and "sunday", each of which might be exactly the same for each and every event that occurs on those days. Why duplicate that data by storing the info in event_date and event_recurrence?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.