Relationship that are optionally more specific

Question

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.

I currently have the following relationship.

CREATE TABLE Events 
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Locations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

ALTER TABLE Locations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Basically, an event can have zero or more locations. What I want now is to associate a donation with either an event, or be more specific and associate it with a location.

CREATE TABLE Donations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL, -- this is optional
    PRIMARY KEY(ID)
)

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

However, if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID. In cases where LocationID is specified, it makes EventID redundant, and therefore it is no longer normalized.

Is the schema I proposed considered good form, or should I make both EventID and LocationID nullable, then enforce a check constraint that either exclusively EventID or LocationID should be set?

Down the road, we will then have registration. Which I am proposing will look similar to this:

CREATE TABLE Users
(
    ID INT IDENTITY(1,1) NOT NULL,
    Username NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Registrations
(
    UserID INT NOT NULL,
    EventID INT NOT NULL,
    LocationID INT NULL,
    PRIMARY KEY(UserID, EventID)
)

ALTER TABLE Registrations ADD FOREIGN KEY(UserID) REFERENCES Users(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Registrations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)  

Essentially, a user can register to an event once (regardless of whether they picked an location or not). The primary key I have is problematic if I make EventID nullable in order to prevent data redundancy with EventID and LocationID.

The current solution we are using to this problem is we do not have a Locations table.

CREATE TABLE Events
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    ParentEventID INT NULL, -- when null it's an event, otherwise it's a location for the eventid it references
    PRIMARY KEY(ID)     
)

ALTER TABLE Events ADD FOREIGN KEY(EventID) REFERENCES Events(ID)

Which solves the EventID and LocationID data redundancy problem. However I find this structure odd as events and locations are conceptually different things. Down the road we may have fields that are specific to events and locations.

To determine whether records in this Events table are an event or location, if the PrimaryEventID field is NULL, then it is an event, otherwise it's a location for that EventID.

Please let me know a good schema for these business requirements.

Answer 1

Instead of:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

I would do this:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID)

That would guarantee that "if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID".

That done, your approach makes sense.

Answer 2

Your proposed design is alright. Being derivable is not quite the same thing as being redundant. Semantically, registering for an event is different from committing to attend that event at a particular location. This is true because everyone who is going must commit to the event, but they don't have to commit to a location.

The only issue that your proposed design should deal with is the potential for logical inconsistency between the chosen location (if any) and the event. This could be handled procedurally by your application, or you could use a solution such as the one suggested by ypercube to impose this constraint declaratively.