2

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?

3
  • 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.
    – freakish
    Commented Jul 2 at 7:42
  • 2
    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. Commented Jul 2 at 10:21
  • 2
    @LucFranken, my thoughts exactly. Consider writing up an answer. Commented Jul 2 at 12:03

1 Answer 1

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.

2
  • 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. Commented Jul 2 at 13:07
  • 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!
    – Sergey K
    Commented Jul 3 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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