Database design: where do I put this data

database-design

EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

This is what I've got now. However, events of some event category type need extra data fields. So where do these fields go? This is what I'm thinking…

EventCategory
=============
EventCategoryId PK,
Name

Event
=====
EventId PK,
EventCategoryId FK,
Name

SpecialEvent
============
EventId PK,
Extrafield

So there's a 1-1 relationship between Event and Special Event (this structure will map to a single SpecialEvent class in C#) and all events and only those events that reference the pertinent category will have a row in this new table.

Is this a fair design? My qualm is that an event will have a a corresponding row in the SpecialEvent table and yet won't reference the required event category or vica versa.

Best Answer

What you are doing here is called specialization.

You can look at this answer to a similar question at stackoverflow which will point you in the right direction. I've implemented a slightly modified version with success.

*An other option (that I do not advise!!) is the use of EAV. *

Edit: I've created an example for your situation. (Don't mind the sample data ;) )

CREATE TABLE EventType (
EventTypeID INT NOT NULL PRIMARY KEY,   
EventType VARCHAR(200) NOT NULL   
);

INSERT INTO EventType (EventTypeID, EventType) VALUES (1, 'Play Rockband'),(2, 'Play Call of Duty');

CREATE TABLE Event (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Event VARCHAR(200) NOT NULL,
    CONSTRAINT UQ_Event UNIQUE (EventID, EventTypeID),
CONSTRAINT FK_Event_EventType FOREIGN KEY (EventTypeID) REFERENCES EventType(EventTypeID)
);

INSERT INTO Event (EventID, EventTypeID, Event) VALUES (1, 1, 'Play the bass'),(2, 2, 'Sniper!');

CREATE TABLE SpecialEvent1 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent1 */
CONSTRAINT CHK_EventTypeID_1 CHECK (EventTypeID = 1),  
/* Check if event exists */  
CONSTRAINT FK_SpecialEvent1_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent1 (EventID, EventTypeID, Extrafield) VALUES (1, 1, 'Expert');

CREATE TABLE SpecialEvent2 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield2 VARCHAR(200) NOT NULL,
Extrafield3 VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent2 */
CONSTRAINT CHK_EventTypeID_2 CHECK (EventTypeID = 2),  
/* Check if event exists */    
CONSTRAINT FK_SpecialEvent2_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent2 (EventID, EventTypeID, Extrafield2, Extrafield3) VALUES (2, 2, 'Character X', 'Team Z');

You can query the events like this:

SELECT EventType.Eventtype AS [Type of event], Event.Event, SpecialEvent1.Extrafield AS Difficulty
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent1 ON Event.EventID =  SpecialEvent1.EventID AND Event.EventTypeID = SpecialEvent1.EventTypeID;

SELECT EventType.Eventtype AS [Type of event], Event.Event [Play As], SpecialEvent2.Extrafield2 AS Character, SpecialEvent2.Extrafield3 AS Team
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent2 ON Event.EventID =  SpecialEvent2.EventID AND Event.EventTypeID = SpecialEvent2.EventTypeID;

SQL Fiddle: http://sqlfiddle.com/#!6/d1911/1/0

Related Question