Postgresql – need better db design for project

database-designpostgresql

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?

Best Answer

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

Related Question