Sql-server – Relationship that are optionally more specific

database-designreferential-integrityschemasql server

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.

Best Answer

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.