I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which have information both common and specific to their categories. From my application, I need to be able to withdraw all events (of both cat a and b for user 1), as well as events specific to their type (e.g all of cat a for user 1 or all of cat b for user 1).
Currently, I am storing the two different categories in their own respective tables, for instance tbl_cat1_events and tbl_cat2_events. A sample is below.
tbl_cat1_events ------------------ event_id user_id start_time end_time cat1_foo1 cat1_foo2 tbl_cat2_events ------------------ event_id user_id start_time end_time cat2_foo3 cat2_foo4
This is problematic for several reasons. First of all, when submitting a new event, I have to query both tables in order to verify there isn’t a conflicting event going on at the same time. Obviously this is caused by both tables containing common information. Furthermore, when retrieving events, I’ll need two queries, selecting events from both tables, after which running a quick sanity check for clashes. Within my code, I’ll also need different data structures for both categories.
How can I improve the structure of my database to be more centralized while requiring less queries and generally being more efficient?
One possibility, stated simply, is the events are all in a calendar table, along with any non-category-specific attributes… then tables for the separate types of events to store the category-specific attributes.
tbl_calendar ------------------ event_id (PK) user_id (FK -> user.id) start_time end_time tbl_cat1_event_info ------------------ event_id (FK -> tbl_calendar.event_id) cat1_foo1 cat1_foo2 tbl_cat2_event_info ------------------ event_id (FK -> tbl_calendar.event_id) cat2_foo3 cat2_foo4
This query would return essentially the same result as a union query of the two original tables.
SELECT * FROM tbl_calendar tc LEFT JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id LEFT JOIN tbl_cat1_event_info c2 ON c2.event_id = tc.event_id WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL; -- see the expanded version of this query below
That last line isn’t technically necessary but it assures that no events that aren’t either in c1 or c2 don’t get returned.
Getting just the cat1 events:
SELECT * FROM tbl_calendar tc JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id;
Follow-up: Avoiding events inappropriately being assocated with more than one category
The business rules say that an event is always either a cat1 event or a cat2 event, never both… but the structure I suggested above does not contain any mechanism for avoiding the inconsistency of the same event_id being inserted in both the cat1 and cat2 event info tables, which would then indicate that one event was both types of events… which can’t be true.
If we add an
event_type_id column to
tbl_calendar, we can also modify the joins so that only the appropriate *event_info table is joined.
-- assumes tbl_calendar has an event_type_id column SELECT * FROM tbl_calendar tc LEFT JOIN tbl_cat1_event_info c1 ON tc.event_type_id = 1 AND c1.event_id = tc.event_id LEFT JOIN tbl_cat1_event_info c2 ON tc.event_type_id = 2 AND c2.event_id = tc.event_id WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;
This would not actually prevent the event from being inserted but it would result in it not being evaluated if it appears in the wrong table; only the correct table according to event_type_id would be joined for each event.
A more aggressive (but perhaps also more correct) approach in addition to the above would be before-insert and before-update triggers on the *event_info tables that checked the other *event_info table and used SIGNAL (introduced in MySQL 5.5) to throw an exception to prevent the incorrect data from being inserted or updated. One example:
DELIMITER // CREATE TRIGGER tbl_cat1_event_info_bi BEFORE INSERT ON tbl_cat1_event_info FOR EACH ROW BEGIN IF EXISTS (SELECT c2.id FROM cat2_event_info c2 WHERE c2.id = NEW.id) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Conflicting value found in cat2_event_info'; END IF; END; // DELIMITER ;