CODEWITHSUNDEEP
sqlmany-to-manyone-to-many
Squadrons
Squadrons

Reputation: 2557

database schema - mixing many to many and one to many relationships

I have a user model and a task model.

A user creates a task. This seems simple: the task has a user_id foreign_key

But now we introduce sharing. Now a task has a many to many relationship with users, but belongs to an owner.

I could: - leave the user_id on tasks (perhaps renaming it to 'owner_id') - AND have the many to many join table (user_tasks or shared_user_tasks) only model tasks that have been shared.

OR I could: - remove user_id on tasks - AND make user_tasks a pure many to many relationship with a boolean 'is_owner' to tell me if that user is the owner

OR I could: - remove user_id on tasks - AND make user_tasks list both the shared_user_id AND owner_id Which means that for some records, the shared_user_id and the owner_id would be the same number (harder queries than the above)

Which is more sound according to best practices?

Thank you for your time.

And, as the first answer suggest (though I'm a bit reticent to accept for some reason), conceptually, the two roles are different. One is a task owner, the other is simply someone who had the task shared with them.

Upvotes: 1

Views: 952

Answers (2)

Transact Charlie
Transact Charlie

Reputation: 2203

Normalisation to the rescue! Probably..... (though there may be scope to denormalise for performance depending on your usage)

Table Users -- Stores information about users (who)

Table Tasks -- Stores information about tasks (what the task is, what it does, etc)

Table UserTasks -- M->M mapping.

Table UserTaskTypes -- Stores ("Owner", "Shared", "Consultant", "Manager", etc)

on UserTasks could look something like this (tsql)

CREATE TABLE UserTasks (
    [TaskID] INT,
    [UserID] INT,
    [UserTaskTypeID] INT,

    FOREIGN KEY FK_UserTasks_TaskID ([TaskID])
            REFERENCES Tasks ([TaskID]),

    FOREIGN KEY FK_UserTasks_UserID ([TaskID])
            REFERENCES Users ([UserID]),

    FOREIGN KEY FK_UserTasks_UserTaskTypeID ([UserTaskTpyeID])
            REFERENCES UserTaskTypes ([UserTaskTypeID]),

    PRIMARY KEY PK_UserTasks ([TaskID], [UserID], [UserTaskTypeID])
 )

And according to your business rules you could add CHECK CONSTRAINTS to enforce that every task only has one owner etc.

Choosing a clustered index for this may be as simple as clustering the PK but you'll shuffle data a lot on insets -- you may want a surrogate autonumber PK and enforce the uniqueness with a UNIQUE CONSTRAINT instead.

Upvotes: 1

woemler
woemler

Reputation: 7169

The "true" correct answer will depend on how these tables are being used, but it sounds like Option #1 should be your solution (task_table.owner_id and a separate M:M user_tasks table). Even though a task owner and task user share the same source table, they are conceptually different and should be treated as such. The logic of the table joins would be cleaner this way as well.

Upvotes: 1

Related Questions