SQL One-to-Many Table vs. multiple one-to-one relationships

Question

I'm working on a project with the following objective: A User can create a Challenge and select an optional Rival to take part of this challenge. The Challenge generates Daily entries and will track stats on these.

The basic User and Entry entities look like this:

CREATE TABLE users (
    id (INT),
    PRIMARY KEY (id)
);

CREATE TABLE entries (
    challengeId INT,
    userId INT,
    entryDate DATE,
    entryData VARCHAR,
    PRIMARY KEY (challengeId, userId, entryDate)
)

The piece I'm having trouble with is the Challenge piece with the Rival concept. I can see two approaches.

// Hard code the concept of a Challenge Owner and Rival:
CREATE TABLE challenges (
    id INT,
    name VARCHAR,
    ownerId INT,
    rivalId INT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (ownerId, name)
);

// Create Many-to-one relationship.
CREATE TABLE challenges (
    id INT,
    name VARCHAR,
    PRIMARY KEY (id),
    UNIQUE KEY (name)
)
CREATE TABLE participant (
    challengeId INT,
    userId INT,
    isOwner BIT,
    PRIMARY KEY (challengeId, userId)
)

The problem with the first approach is that referential integrity is tough since now there are two columns where userIds reside (ownerId and rivalId). I'd have to create two tables for everything (owner_entries, rival_entries, owner_stats, etc.) in order to set up foreign keys.

The second approach solves this and has some advantages like allowing multiple rivals in the future. However, one thing I can't do anymore with that approach is enforce Challenge name uniqueness across a single user instead of the whole Challenge table. Additionally, tasks like finding a Challenge's owner is now trickier.

What's the right approach to the Challenges table? Is there anyway to set up these tables in a developer friendly manner or should I just jump all the way to Class Table Inheritance and manage the concept of Owner/Rivals there?

What database are you using? We do multiple foreign keys to the same pk field all the time in SQL Server. That removes your objection to the first approach. The second approach may still be better if you will ever need to have more than just 2 people involved in a challenge. — HLGEM, Aug 24 '11 at 22:37
His real objection isn't setting up multiple fks to the same pk field (he's doing that anyways), it's setting a single fk field to multiple pks. I don't think this is possible in general in any case, besides being rather problematic... — Clockwork-Muse, Aug 24 '11 at 22:44
I've often used triggers in SQL Server to enforce peculiar uniqueness requirements, e.g. "username" need only be unique among active accounts. — HABO, Aug 24 '11 at 23:14

score 1 · Answer 1 · answered Aug 24 '11 at 22:41

I think the way I would set this up is as follows (using the second approach):

CREATE TABLE challenges (id INT, 
                         name VARCHAR, 
                         owner_id INT, 
                         PRIMARY KEY (id),
                         UNIQUE KEY (name, owner_id))

CREATE TABLE participant (challengeId INT,
                          userId INT, 
                          PRIMARY KEY (challengeId, userId))

This allows easy tracking of who owns the challenge, yet extracts out the individual participants.
This would also allow you to unique the challenge name by the owner safely, and foreign keys on the userId in participant are easy. 'Rivals' are then all participants that are not the challenge owner.

score 0 · Answer 2 · answered Aug 24 '11 at 22:33

0

I treat the first approach the right one. You could have one table for users and one for challenges.

Are you aware that you can reference one table twice like below?

SELECT * FROM CHALLENGES 
INNER JOIN USERS AS OWNERS ON OWNERS.ID = CHALLENGES.OWNERID
INNER JOIN USERS AS RIVALS ON RIVALS.ID = CHALLENGES.RIVALID

In this case you can reference both rivals and owners without creating new tables.

answered Aug 24 '11 at 22:33

Anton Vidishchev

1,334
12
17

Getting the Users like that is easy, it's the Entries and possibly Stats where I'm concerned. Take the Entry model I described, how do I track User Entries in a single table while enforcing referential integrity so that only users that belong to a challenge can make entries? It seems like I need a OwnerEntries table with challengeId and ownerId and then a RivalEntries table with challengeId and rivalId. – Cowlby Aug 24 '11 at 22:36
In this case, you could add a stats table referencing the challenges, depending on what data is exactly needed. Or, you could apply one of audit patterns to you table: check out [this](http://stackoverflow.com/questions/3823/suggestions-for-implementing-audit-tables-in-sql-server) post. – Anton Vidishchev Aug 24 '11 at 22:43

SQL One-to-Many Table vs. multiple one-to-one relationships

2 Answers2