Database design: where do I put this data?

Question

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.

Answer 1

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

Answer 2

You are very close to how this should be done, but instead of creating a separate table for SpecialEvent. I would create a table for EventType and then each event would be assigned a type. Your structure would be similar to this:

create table EventCategory
(
  EventCategoryId int, -- PK
  name varchar(20)
);

insert into eventcategory values
(1, 'category 1'),
(2, 'category 2'),
(3, 'category 3'),
(4, 'category 4');

create table EventType
(
  typeid int,  -- PK
  name varchar(20)
);

insert into eventtype values
(1, 'Type 1'),
(2, 'Type 2'),
(3, 'Type 3');

create table Event
(
  eventid int,  -- PK
  eventcategoryid int,  -- FK
  eventtypeid int,    -- FK
  name varchar(50)
);

insert into event values
(1, 1, 1, 'Event 1'),
(2, 1, 3, 'Event 2'),
(3, 1, 1, 'Event 3'),
(4, 2, 2, 'Event 1'),
(5, 2, 1, 'Event 2'),
(6, 4, 3, 'Event 1');

Then to query the tables, you would just join on the additional table, similar to this:

select e.eventid,
  e.name EventName,
  c.name CategoryName,
  t.name EventTypeName
from Event e
left join EventCategory c
  on e.eventcategoryid = c.eventcategoryid
left join eventtype t
  on e.eventtypeid = t.typeid

See SQL Fiddle with Demo

Answer 3

The solution you thought about, solves the problem, but is not the best answer. What if you have to add columns to another event type? Based on this solution you have to add another 'Special Event' table for that which eventually creates a mess. My suggestion is that you define a separate table for the event which has got special columns (the extra columns). So you would have the 'Category Event' table as the parent, 'Event' table which contains the records that have the same structure and a new table, say 'Different Event' which again has foreign key to parent, 'Category Event' table, along with all columns as required.

Edited based Ian's comment:

Isn't that the same design as the one proposed in my question?

It is not exactly the solution you offered. What I meant is, you need to create a table to put the common columns in there and have a FK to this table from the Event table(s). It would help you to avoid adding the common columns to more than one table, when they are shared between more than one Event tables:

create table EventCommonColumns
(
  eventCommonColumnsId int, -- PK
  Col1 varchar(20),
  Col2 varchar(20),
  Col3 ....
);

create table Event1
(
  eventid int,  -- PK
  eventcategoryid int,  -- FK
  eventCommonColumnsId int,    -- FK
  name varchar(50),
  extraCol1_1,
  extraCol1_2,
  ...
);

create table Event2
(
  eventid int,  -- PK
  eventcategoryid int,  -- FK
  eventCommonColumnsId int,    -- FK
  name varchar(50),
  extraCol2_1,
  extraCol2_2,
  extraCol2_3
  ...
);

Answer 4

Your design is reasonable and may be preferable depending on how many and how large the columns in SpecialEvent will be. If they are small and few then they might as well be in the Event table itself (as Phil said). Such a design would be simpler and not sufficiently slower to merit concern. The development time you save will likely be better served optimizing something that true is slow.

You should not create a separate table for each special event. This would add repetition, overhead, and a general sense that design is an afterthought not a planned activity.