How to design sharing feature properly in DB?

Question

My DB has three tables:

CREATE TABLE Users (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL
);
CREATE TABLE Categories (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 user_id INTEGER NOT NULL,
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);
CREATE TABLE Notes (
 id INTEGER PRIMARY KEY,
 description TEXT NOT NULL,
 category_id INTEGER,
 user_id INTEGER NOT NULL,
 FOREIGN KEY (category_id) REFERENCES Categories(Id),
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);

I want to implement functionality to get user an ability to share note he created with another user. I ended up with a solution to have junction table:

CREATE TABLE SharedNoteToUserId
(
 note_id int NOT NULL,
 user_id int NOT NULL,
 CONSTRAINT PK_SharedNoteToUserId PRIMARY KEY
 (
 note_id,
 user_id
 ),
 FOREIGN KEY (note_id) REFERENCES Notes(Id),
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);

But I also want to have endpoint to retrieve list of all categories including categories from shared notes. It looks like it's just union of two tables:

SELECT Categories.id, Categories.name
 FROM Categories 
 WHERE Categories.user_id = 2
UNION
SELECT Categories.id, Categories.name
 FROM SharedNoteToUserId
 JOIN Notes ON Notes.id = SharedNoteToUserId.note_id
 JOIN Categories ON Categories.id = Notes.category_id
 WHERE SharedNoteToUserId.user_id = 2;

But I'm not sure that it's a right approach. Maybe I need to create and manage separate junction table for categories as well?

• freakish: That is correct approach. It won't scale well, but that is a problem for later. In particular separate table will perform better, but now you have another place where you have to ensure consistency. Which is painful. So write everything in a way that it is simple but also easy to modify later on, and you will be fine.
• Luc Franken: If you consider it as "userHasAccessToNote" you can also create an entry for the initial user who created the Note. Then all access is handled in the same way. For example when you want to transfer access to another user. In your Note entity you could have a field: createdByUserId if that value is worth something for your business logic.
• Greg Burghardt: @LucFranken, my thoughts exactly. Consider writing up an answer.

Answer 1

As requested in the comments as an answer:

CREATE TABLE Users (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL
);
CREATE TABLE Categories (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 user_id INTEGER NOT NULL,
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);
CREATE TABLE Notes (
 id INTEGER PRIMARY KEY,
 description TEXT NOT NULL,
 category_id INTEGER,
 created_by_user_id INTEGER NOT NULL,
 FOREIGN KEY (category_id) REFERENCES Categories(Id),
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);
CREATE TABLE NotesAccess
(
 id INTEGER PRIMARY KEY,
 note_id int NOT NULL,
 user_id int NOT NULL,
 /* Optional: Add some more detailed rights like readOnly, etc */
 FOREIGN KEY (note_id) REFERENCES Notes(Id),
 FOREIGN KEY (user_id) REFERENCES Users(Id)
);

A single table to manage all access to the notes. When you create a note in a transaction also add the initial NotesAccess record so the creator gets access to the Note.

This allows for a flexible access control, is extensible for example with different roles of access (writer, reader etc).

It is a consistent interface and easily queryable to get a list of notes with its categories and users.

• Greg Burghardt: Yup. select from Notes join NotesAccess where NotesAccess.user_id = X and Notes.created_by_user_id = X gives you all the notes you created. Omit the created_by_user_id condition to get all the notes a particular user has access to. Easy-peasey rice and cheesy.
• Sergey K: I very like this approach! I also think to have CategoriesAccess table to be able to get all available categories for user quickly. Thanks for help with that!