Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You should show what you have attempted and why it didn't work. Open ended questions like "what is the most efficient way" are not a great fit for this question-and-answer style site. –  Max Vernon Oct 19 '12 at 21:33
1  
I have shown what I have attempted and stated the problems with it. I have also edited the question to try and improve the clarity and directness of the question. –  AaronDS Oct 19 '12 at 22:31

1 Answer 1

up vote 1 down vote accepted

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 ;
share|improve this answer
    
Hi, thanks for this response, much appreciated. One question - is it possible to ensure that an event is only linked to one category event within the database? –  AaronDS Oct 19 '12 at 22:32
    
I've added some thoughts on this point, above. –  Michael - sqlbot Oct 20 '12 at 2:29
    
Great stuff, thanks a lot. –  AaronDS Oct 20 '12 at 9:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.