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
Best Answer
If the only reason not to add columns is "but that would make the database design, in documentation, inconsistent with the columns present in the database" then you need to look at your procedures.
If there is a business reason to add the fields and your systems support it then you need to have your documentation updated at the same time as the table. This should not be much more than adding a description fo the field to the documentation, perhaps having a place in documentation just for these user added fields.
Note that the business wants the system and the documentation is only a tool to help and not the end result.