Is this an adequate primary key?

Question

I have a table of Events and a number of subtables of different types to define each event.

Event table has an ID, a (potentially non-unique) Datetime, userId, and an event typecode.

For the Event Type tables, say ExerciseStart... is EventId an adequate primary key? Each event is only one type, so this should never double up. And is it useful to include the datetime in the subcategories, even perhaps as a compound primary key to avoid accidental doubling up and potentially make query writing easier? (though it would also be redundant I feel).

(Unless I were to use Datetime and event typecode as a compound key, but that seems more potentially risky to me)

Answer 1

I think what you need is called class table interitance: you have a envent_table that contains all common properties and many tabele for different types of events.

It could be something like this:

CREATE TABLE events(
    event_id SERIAL PRIMARY KEY,
    ... other fields
);

CREATE TABLE event_typeN(
    event_id BIGINT UNSIGNED PRIMARY KEY,
    ...other fields,
    FOREIGN KEY(event_id) REFERENCES events(event_id)
);

Answer 2

Using Standard SQL...

The usual way to do 'inheritence' or 'subclassing' is to have a compound key on (event_type, event_ID) and use this key in the referencing 'subtype' tables with a CHECK constraint to ensure the event_type is appropriate for that 'subtype' e.g. CHECK (event_type = 'Exercise start').

An additional touch is to make the event_type DEFAULT in the 'subtype' table again match the appropriate type e.g. event_type VARCHAR(20) DEFAULT 'Exercise start' NOT NULL.

The structure could look like this:

CREATE TABLE EventTypes 
(
 event_type VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE Events 
(
 event_ID CHAR(10) NOT NULL, 
 event_type VARCHAR(20) NOT NULL
    REFERENCES EventTypes (event_type), 
 event_date DATE NOT NULL, 
 PRIMARY KEY (event_type, event_ID)
);

CREATE TABLE ExerciseStartEvents 
(
 event_ID CHAR(10) NOT NULL, 
 event_type VARCHAR(20) DEFAULT 'Exercise start' NOT NULL
    CHECK (event_type = 'Exercise start'), 
 FOREIGN KEY (event_type, event_ID)
    REFERENCES Events (event_type, event_ID), 
 PRIMARY KEY (event_type, event_ID), 
 exercise_description VARCHAR(30) -- etc --
);

However, there is a big problem with mySQL in that it does not enforce CHECK constraints :( [How anyone can tolerate this situation is beyond me!] So to answer your question, having a simple key on event_ID alone is not adequate because you can't enforce the appropriate type in the 'subtype' tables.

While it may be tempting to 'promote' event_ID to be a candidate key in the subtype table, this may not be a good idea. Although it would allow only one row for a given event_ID to appear in the table, if that row is of the wrong type it would prevent the row with the correct type from being inserted!

Solution? Move to a better SQL implementation ;)