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.
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 would be preferred.
The event table holds basic information important for searching, like the location, description, event category.
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.
Holds information about the max capacity (current cap. calculated based on purchases) and the price of the current event.
Event named escape room. Has a description, images, etc.
- Basic - no extra cost, lasts 1h
- Extended - more sub-rooms unlocked, lasts 2h. Costs 5$ extra
Every tuesday and friday at 18:00 (stored in
event_recurrence), with special dates for some holidays (stored in
- 20$ during summer holidays, 15$ the rest of the year (recurring dates). Certain dates can cost extra (christmas, easter...).
Basically this rustles my jimmies in two different ways:
event_configneed a reference to
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.
event_configan unneeded complexity? Its sole purpose is to provide shared fields to
event_recurrence. Wouldn't it be better just to have the
capacitycolumns both in