need better db design for project

Question

I have two tables:

1. notes
2. users

Notes Table:

id || description ||CreatedBy(which is userid)||......other columns
---------------------------------------

Users table:

id || name || ......other columns
---------------------------------

Now I have a scenario, in which a user can create a note and share it with other users. Each user can create a note and then can share this with other users.

To Implement the above task, I will create a new table 'UserNotes'.

UserNotes Table:

id || userId || NotesID
-----------------------

But suppose I have one note which I want to share with all other users then my usernotes table will have 100 entries. Similarly if I have 1000 notes and all notes are shared with all users then my new table will have 1000*1000 entries.

Also a user who has created a note and shared with other users, can remove the sharing also for some users. For example: if a user creates a notes and shared with 100 users initially, he can later on remove some user associations or add some new users also.

Is my approach correct?

Or is there a better approach to achieve the above scenario considering ease of insert, update and select?

I am using POSTGRESQL as my RDBMS.

Can you please help?

Answer 1

I think the Notes table is missing a userID FK column (and put an index on it)
You need to identify the owner of the Note

The UserNotes table does not need an ID
just:

NoteID || UserId

That is a sufficient composite PK
And I would do it in that order for quick counting NoteID
select count(*) from UserNotes where NoteID = X would be very very fast

So what 1000 x 1000 rows. You have 1000 x 1000 unique relationships. It is just two Int.

One trick is have a UserID Public ID 0 for all

Just a preference but I don't use plural in a table name
Tables are used to store many rows

Answer 2

tl;dr - Currently you have a notes table, a user table, and an "allowed to see" table ... which is fine. Consider a "public flag" and adding a "not allowed to see" table.

Your approach looks fine to me. Alternately, you could have an additional column on your NOTE table called "is_public" ... if that is set, then the note is a public note and available for everyone to see (thus avoiding your 1000*1000 scenario) ... also, you could have one final "exclusion" table so that a person could make a note public but then pick specific people who should NOT be able to see it.

I'm not advocating one way or the other, since I think both are perfectly fine to use. You do need to make sure your join tables are properly indexed, though, otherwise queries will start to get slow.

Frisbee has one important bit in his answer - your UserNotes table does not need an ID of it's own.. Join tables usually just have the PK as the composite set of IDs.. (so PK would be UserID/NoteID)