Normalize repeated indefinite ongoing dates for an event

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

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.

StackExchange Database Administrators Q#143166, answer score: 3