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
There really is a whole raft of pros to normalizing your database. If the main issue you're having is naming, then you're in pretty good shape. Apart from space which you've already mentioned:
- The width of your table will be smaller, this will increase the speed of your selects.
- Updates and inserts will be quicker because you're inserting/updating integers instead of text
- Maintaining your data will be easier ie if there was a new type of school which needed to be added, you should just be able to add it to the table.
- You can create hierarchies in your lookup tables
- You don't need to reply on your application layer to enforce referential integrity.
- You can manage changes to your lookup options over time (ie what is "Public" is no longer applicable after July 1st but older records created before this date must still show this as an option?)
- Loading data into a data warehouse is simplified
- It makes it easier to other DBAs to understand your schema
Normalization is usually going to have a level of subjectivity. You database schema should reflect a balance between maintainability, complexity and performance. On the flip side, if you're only going to have 1 or 2 tables and a small amount of records, maybe normalization is overkill? I would tend to normalize anyway, because if the database needs to scale up, you're already in a good position to do so.
As for naming, have a think about the most sensible name that will describe the table's contents without it sounding crazy like the example you gave above. Otherwise you'll end up with a schema which is hard to understand.
Hope this helps.
Best Answer
If I understand your scenario properly, the following assertions are relevant:
Cat
has one-to-manyOwners
or, put another way, anOwner
has one-to-manyCats
.Cat
is involved in one-to-manyVisits
to one-to-manyVets
.Visit
to one specificVet
, oneCat
is taken by one-to-manyOwners
.From this set of assertions I have derived (and uploaded to Dropbox) the following Cats and Vets Data Model, which depicts the way I would approach your specifications.
As you can see in such IDEF1X[1] data model, I consider important to add an entity that represents the specific event when a
Cat
(brought by one-to-manyCatOwners
) visited a particularVet
, and I called itVisit
.And yes, I also think that the
CatOwner
entity (that you have alredy included in your diagram) is very useful since, in addition to preventing aPerson
who is not anOwner
of a particularCat
from taking suchCat
to aVet
, it may also serve the purpose of relating aCat
and one-to-many of itsOwners
to a specificVisit
to an individualVet
.It is also necessary to add a many-to-many relationship to provide the advantage of associating one-to-many
CatOwner
ocurrences to one-to-manyVisit
instances (eachVisit
received by one particularVet
), and I propose the relationship calledCatOwnerVisit
to cover this requirement.Note
1. IDEF1X is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the U.S. National Institute of Standards and Technology (NIST).