5

I am designing a calendar database that has a lot of booking and billing information. The database itself consists of form records, which are editable, and only when an event is confirmed by staff it appears on the web calendar. The web calendar does not have a separate table, it merely queries the database for recent confirmed events.

Originally the submission form was used by both staff and public and went into one giant table. I've split the booking form data into the following tables in order to normalize it:

`form_control`
    form_id
    default_event_date_id     (usually same as primary)
    primary_event_date_id     (foreign key event_dates)
    primary_event_host_id     (foreign key event_speakers)
    overall_start_date_time
    overall_end_date_time   
    interval_if_ongoing       (e.g. "1:MW," = first mon/wed of every month)
    is_confirmed              (bool)
    visibility                (bool) 
    billing_info etc.

`event_dates`
    event_id
    event_form_id       (one-to-one or many-to-one form relation)
    event-specific info (description etc.)

`event_speakers`
    person_id
    event_date_id           (many-to-one event relation)
    name, contact info etc. (specific to form submitted)

`ongoing_dates`
    date_id
    form_control_id       (check if confirmed & overall visible, get default event_id)
    custom_event_date_id  (if date is the primary, sole, or custom event date; else NULL)
    start_date_time
    end_date_time
    is_happening          (NULL = reserve 0 = dark/unused 1 = scheduled)

form_control: contains data that pertains to the entire event request or series.

  • "default" start date, end date; these limit number of events and describe a default event time.
  • ongoing interval (if end date > start date): this is my main problem (see below).
  • "default" calendar description.
  • visibility options; determines if and when the event appears on web calendar.
  • confirmation status; determines if the event appears on the calendar at all.
  • rates and billing options; these are currently series-specific and pertain only to the master form. To make them date-specific (not currently desired) I'd have to separate them into another table, but these data are logically isolated from the rest of the form.

(Not sure if worth it to handle edge cases, but I assume joins are not required to simply rebuild each form, if the following tables are strictly dependent. I.e. if rates were separated out, I would already know the form_id for a given billing_info or event_date and simply SELECT for it, correct?)

event_dates: describe calendar info pertinent to each event date.

About 80% of events only have one date. Per the above: Should the "default" (i.e. primary) event_id for each series be placed in form_control? Should it be a boolean (is_primary, is_default) in event_dates? Or should it be a field in event_dates that is UNIQUE foreign key (use_primary = form_id), default NULL? (to mark it as the default, or primary record) I want to structure it so that the php can't accidentally cause data integrity issues by setting non-unique default child record ids (primary date, default billing) for each form.

event_speakers: speaker info unique to a given date or form (as submitted).

The idea here is that old info from the date-of would be preserved even if the canonical info for a given speaker in the contacts db is updated. the form could AJAX query the contacts db to allow read-only hinting if a speaker appears multiple times, in which case the info listed in the form would be overwritten with the then-existing contact info for the speaker and a reference to the unique key.

event_speakers would be many-to-one relational to each event date. If a speaker is overall host, then they would be listed as default for the entire form, but I am not sure where best to put this, as a flag or foreign key (same question as above.)

Like primary event date, primary host info would be unique to each form, one-to-one, but would be in the same format / table as all other event_dates or speakers respectively. Perhaps I should separate them into a cloned table for primaries only? The problem with that is, the client may wish to allow a different speaker to be made primary, or allow a different event in a series to be the primary date without having to INSERT/DELETE anything.

OK, so here's my main problem:

ongoing_dates: list all actual dates in which the event occurs, queried by the web calendar.

I currently have event_dates as listing custom event info only, i.e. dates with something different going on than the primary event_date -- different lineup, different description, or different hours. (Different lineup would simply be a reference to different event_date_id in event_speakers).

However, this leaves me with ongoing_dates that simply repeat at intervals (some ad infinitum). I can put the repeat-interval (or date-mask, e.g. mTW) in form_control, but this only helps to set dates, not retrieve them.

And I realized I needed to track dates that are not in the calendar, such as "every second wednesday EXCEPT July 1st. ALSO INCLUDING July 20th."

I figure the best way of handling this is to use the info in form_control to set or generate dates but then write a record in ongoing_dates upon-submit for every custom or blackout date, (otherwise scheduled dates for which it is reserved, or dark), with default = reserved. (reserved would be treated as a suggestion if the event is not yet confirmed, i.e. pending).

That way, only primary, default, or custom dates (dates for which there is any data other than "it's happening again") would have their own event_dates record created. The rest would go in ongoing_dates.its_happening to be simply queried by the web calendar. Possible values for its_happening would be e.g. "it's happening again", "it's not happening this particular interval", or... "it may be happening", which can be represented as a simple TRUE-FALSE-NULL.

ongoing_dates would also have a column for: event_start and end_time (using form_control start and end time as default, allowing custom times without creating a record) and, importantly, an optional event_dates foreign key. (i.e. every event_dates record would have a 1-1 relationship with ongoing_dates, but 99% of ongoing dates would not have a separate event_dates record, unless one is created by simply adding the ID.) This way the calendar can add and delete ongoing_dates at will without losing any information (e.g. if the exact same event is made once a month instead of twice a month)

My question is, does this approach make sense from a normalization standpoint?

Should I separate time and visibility info entirely into ongoing_dates, so that each event_dates record refers to a date that is only listed in ongoing_dates? Or must every date in the calendar have its own full record (with mostly-default information) and the calendar queries only that? Or does it not make sense to have a separate (small) record for potentially indefinite repeating dates?

Those seem to to be my three options. I don't want the database to fill up with extraneous info simply because one event happens every day of the week.

  • 1
    What's the difference between an event date and an ongoing date? Why do you need two tables for this? – Andrew Brennan Jul 12 '16 at 8:27
  • 1
    @AndrewBrennan The whole thing is that he wants to split frequent and one-time events in sake of saving space. Furthermore, – 3manuek Jul 14 '16 at 16:51
  • @3manuek that is correct; I am curious, what were you meaning to say? (in your comment) – Ber Jul 20 '16 at 16:57
  • @AndrewBrennan per the accepted answer, it appears I don't; however, if any MysQL logic were attached to specific ongoing dates, I would need a table for ongoing_dates that include all event_dates as a relational subset (per 3manuek) because ongoing dates for a (single) event are ephemeral, with very little info attached, whereas custom or single event dates are canonical, and potentially describe the entire event. Does that make sense? – Ber Jul 20 '16 at 17:16
2
+150

To focus on ongoing and future dates...

  • A table with suitable columns for simple cases of repeated actions. One row per repeating event. No specific dates except start and end.
  • A table for exceptions (extra dates, skipped dates, etc). This has a column with a specific date. This table could also handle simple, one-time, events.
  • Application code that reads those tables, and computes whether or not one of those events occurs on a particular day.

One important point here is: Do not try to do all the work in SQL; leave the messy stuff for the 'real' programming language in your application. The database is just a "repository of information" and a "source of truth". That is, store the raw data there (and only there).

Once you have made the split between what can and should be done in the database versus what can and should be done in the app, you may find the need to make minor tweaks in the boundary between them.

I don't think we can make all the decisions before starting. Instead, I like to plan to have a mid-course-correction.

  • Sorry for the delay in accepting this answer; I was AFC for a week when the answers were posted, and couldn't seem to log on. I've accepted this answer since it appears to suggest a specific solution, although both answers were helpful. – Ber Jul 20 '16 at 17:00
  • Rick -- to clarify, you're saying I should describe the calendar using as little DB-internal logic as possible, right? so basically have a table for event_dates that includes only exceptions and one-time dates, and refer to form_control to generate ongoing dates, since those should follow a repeating pattern by definition. I could also customize it further by doing event_speakers the same as event_dates in this scenario, so that custom lineups may refer to a range of dates, using application logic to get the lineup (event_speakers one row per-speaker) – Ber Jul 20 '16 at 17:26
  • Yes, that sounds about right. Start a new Question with table definitions based on that concept. Be sure to include the SELECT to grab the data. – Rick James Jul 20 '16 at 18:29
  • Perhaps 2 tables: One for repeated events (start, optional end date, frequency). Another for exceptions (date, add/remove). Read the repeat, then apply the exception(s). – Rick James Jul 20 '16 at 18:31
  • I just realized, I will need to structure the repeated events so that they get "locked in" at some point. Otherwise if, say, an event happens once a month for a year and the schedule changes mid-year, the existing dates will disappear. But this is a problem that still happens if there were a separate table for every single ongoing_date. Since, as you suggest, that would require the DB contents to change to account for mere logic of repetition, so changing the frequency would cause it to go back and remove dates, but that could more easily be applied to only future dates. – Ber Jul 21 '16 at 14:34
3

I think that at the end, event_dates will need to be partitioned. That will allow you to archive old events.

form_control may need this also, and probably primary_event_date_id could be a candidate.

I think, however, that the key from the ongoing and events, should be located at the same way in the form_control. For doing that, you will need to convert the ongoing event to an event_id (you can use MySQL schedulers). That will decrease the complexity of the form_control with the counterpart of increasing the events.

  • 3manuek, I appreciate your answer, can you clarify a few things? thanks! ** is (vertical?) partitioning by date something that can be done automatically using an id (primary_event_date_id) as a key? ** do you mean to suggest use every ongoing_event id as an event_id (if I do not simply combine the two and forego the logic, per the accepted answer) so that they both refer to the same event_id index, and "use MySQL schedulers" (I'll have to look that up, thanks) to auto-increment event_id? thanks. – Ber Jul 20 '16 at 17:07
  • 1
    Horizontal partitioning is pseudo automatic, you can schedule the partition creation beforehand with scheduling or manually. Once you create the partition, the routing is automatic, and write statements are the same as without partitioning. The schedulers can create the events 1 week beforehand, using a custom procedure. @Ber – 3manuek Jul 26 '16 at 0:07
0

Possible solution to account for the helpful info in the above two answers:

`form_control`
 form_id
 primary_event_id          (foreign key in `event_dates`)
 default_event_id          (usually same as primary date)
 primary_event_host_id     (foreign key `event_speakers`)
 default_start_date_time   (actual or arbitrary start point)
 default_end_date_time     (acts as a universal cutoff, backstopping any ongoing dates)
 is_confirmed              (bool)
 visibility                (bool) etc.

`specific_dates`
 event_id
 form_id
 start
 until
 date_status (null = pending/reserve; 0 = blackout/open date; 1 = booked)
 custom_date (0 = regularly scheduled but with custom info, 1 = custom date)
 etc. (event specific info -- if the event_id is listed in form_control as "primary" 
 or "default", its info applies to all ongoing events without their own event_id; so the 
 application pulls only the primary or default event_id to get info about repeat dates) 
 (event_id can be created for any arbitrary date, whether listed as ongoing or not.)

`ongoing_dates`

ongoing_id            (candidate key)
form_id               (foreign key used to SELECT any ongoing dates for said event)
ongoing_interval (e.g. "1:MW," = first mon/wed of every month): actual interval, as a mask
apply_start_date (overrides any previous ongoing for THIS form_id; DEFAULT TO CURRENT TIME 
                 at a minimum, to prevent prior dates from vanishing; does not override 
                 prior `ongoing_dates` which are similarly superseded.)
     (each `ongoing_dates` record has only a start point, no endpoint; and does not record 
     dates individually. the most recent applied interval is used to generate dates past 
     that point, and ends with the overall default_end_time listed in form_control, past
     which point, the entire event ends)
apply_end_date (apply only if there is a gap of no scheduling between dates in a series)

So each event listed in form_control can have multiple ongoing_dates, in intervals that supersede one another. I can do an event_lineups table similarly, to refer to specific listed speakers in event_speakers that may appear in different lineups on different days (in edge cases), with each lineup describing a start date for that lineup, unless superseded by a different lineup (listed speakers in event_speakers would still be one-record per-host PER-overall-event, so all tables would only need to relate to form_control, not to each other):

SELECT default_event_id from `form_control` f where f.form_id = X

SELECT * from `specific_dates` e where e.event_id = DEFAULT_ID and e.form_id = X

# to get default event listing

SELECT * from `ongoing_dates` o where o.form_id = X SORT BY apply_start_date

# go thru the overlapping apply_start_dates to get actual date intervals

SELECT * from `event_lineups` l where l.form_id = X SORT BY apply_start_date

# go thru the overlapping apply_start_date in event_lineups to get the "default" lineup 

# "snap to" non-custom dates somehow to match intervals up with generated dates 
# have apply_start_date "snap to" either a fixed custom event_id or the nth interval? hmm

...

SELECT * from `specific_dates` e where e.event_id <> DEFAULT_ID and e.form_id = X

# grab all custom dates and array merge them with generated dates after 
# grabbing the associated custom lineup (if any); if an event_id is marked as not a custom 
# date and does not line up with dates in a series, allow user to snap to an existing date
# in the calendar control app, or keep the date as-is (custom date).
  • @RickJames I could separate all_dates into separate table with only dates, including ongoing dates, and tie event_id foreign key to any dates with custom info, but this might violate normalization, since DB would need to remember whether a date was "regularly scheduled" and if that schedule changes, the repeat dates would need to change in order to match. If I use a mask of some sort with start point, it'd simply generate non-custom dates on the fly, starting from that date. eg. overall start -- primary / custom date -- apply start for repeated events -- generate repeated -- overall end – Ber Jul 21 '16 at 16:27

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.