Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why does LocationID have to be tied directly to an event? Why not have a Locations table that is independent of Events, and then a junction table like EventLocations? Then a registration can point to an EventID and optionally an event/location combination. Sure, there's redundancy there, but that's driven by wishy-washy requirements to some degree. :-) –  Aaron Bertrand Jan 27 '13 at 20:25
    
This is a SaaS solution. We do not reuse locations for new events. –  Matthew Jan 27 '13 at 20:41
    
To elaborate, a location only makes sense in the context of that the event it's associated with. –  Matthew Jan 27 '13 at 20:47
    
The "User registration" issue can be solved by changing the (3rd) FK to: ALTER TABLE Registrations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID). This allows you to have Locations.EventID as NOT NULL. It also avoids a user having registered for an event and a location that don't relate. –  ypercube Jan 27 '13 at 22:55
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.