Postgresql – Design: similar tables and foreign keys

database-designforeign keypostgresqlrdbms

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?

Best Answer

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?

Related Question