Relationship that are optionally more specific

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)

Essentiall

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.

StackExchange Database Administrators Q#33618, answer score: 2