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.
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.

share|improve this question
1  
If there's a 1-1 relationship between Event and Special Event I see no real gain from having a separate table. –  Phil Oct 14 '12 at 6:24
    
If both tables were joined then events which were not special events would have to pad out the unnecessary columns with nulls. Thus the table wouldn't be in first normal form. –  Ian Warburton Oct 14 '12 at 22:06
    
People get way too hung up on normal forms. They aren't always appropriate for a given situation. Databases 101 != The real world –  Phil Oct 14 '12 at 22:47
    
The OR mapper I'm using 'LLBLGen' does allow one to cram a hierarchy of types into a single table seamlessly but that still leaves my main qualm from the question alive. –  Ian Warburton Oct 14 '12 at 23:07
    
This highlights one of the big problems these days. There is a massive gulf between the likes of Hibernate/random OR library and traditional database design. Would I create an extra table for a 1-1 mapping that adds 2 extra columns of data and forces a join every time? Probably not in this case [given the limited information given]. –  Phil Oct 14 '12 at 23:13
show 1 more comment

4 Answers 4

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

share|improve this answer
    
Can you use this answer? –  Ruud van de Beeten Nov 19 '12 at 13:15
add comment

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

share|improve this answer
    
Adding a new table which determines the type,tells you what the type of the Event is, but it does not accommodate the extra columns that are only applicable to one specific Event. Where are the 'Extra Fields' referred in the question stored now? –  Sky Oct 14 '12 at 21:02
add comment

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.

share|improve this answer
add comment

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
  ...
);
share|improve this answer
    
But then both 'Event' table and 'Different Event' table would duplicate the base columns that they both have in common. If I want to add a new base column then I'd need to do it in two places. –  Ian Warburton Oct 14 '12 at 22:57
    
Ian, this is the fact based on your scenario. If you aren't happy with it, there is another option: Adding a table which contains the shared columns. This table will sit between the 'Event' and 'Category Event' table. In this table you will have only shared columns. –  Sky Oct 14 '12 at 23:17
    
Isn't that the same design as the one proposed in my question? –  Ian Warburton Oct 14 '12 at 23:22
    
Not exactly. Please refer to my edited answer below. –  Sky Oct 15 '12 at 1:26
    
OK not exactly the same. :) But firstly, the columns 'eventcategoryid' and 'name' are shared by both 'Event1' and 'Event2' so they should be in 'EventCommonColumns'. Secondly, the relationship between 'Event1/2' and 'EventCommonColumns' is 1-1. So there's no need for 'eventCommonColumnsId' - just have the primary key of 'EventCommonColumns' be the same as that of the corresponding row in the event specific table. When one makes these changes then I think you have the same design that I proposed in the question. –  Ian Warburton Oct 15 '12 at 2:52
add comment

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.