1

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?

Improve this question
1
  • Can you wait a week or 2 for PG 9.5? It has built-in Row Security
    – Neil McGuigan
    Dec 10, 2015 at 0:02

2 Answers 2

Reset to default
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

Improve this answer
11
  • I think the Notes table is missing a userID column I was assuming that was part of the ".... other columns"
    – Joishi Bodio
    Dec 9, 2015 at 17:21
  • absolutely ...notes table has userid ..i.e. who created the notes.. which is implicit as @JoishiBodio said ...
    – Pranav
    Dec 9, 2015 at 17:25
  • @JoishiBodio Assume all you want. I think you should list the important columns.
    – paparazzo
    Dec 9, 2015 at 17:25
  • @Frisbee :- consider a facebook post and like scenario ... a post can have 50 likes ...later on some one came an unlike the post... are they following the same db approach to relate post and like ...because a post can have 100000 of like ...!! ... just like that user and notes are like cross product ....
    – Pranav
    Dec 9, 2015 at 17:26
  • 2
    @Pranav List out FK key columns. The owner of a note is not just secondary information.
    – paparazzo
    Dec 9, 2015 at 17:27
0

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)

Improve this answer
6
  • Thanks for answer ..actually the situation is if user selects only 50 users to show and 50 not to show ... then again same situation will happen .. i mean 50 entries in exclusion table. Cannot i save userid as json or comma separated value ...do you see any disadvantage in that approach ?
    – Pranav
    Dec 9, 2015 at 17:06
  • Whoever gave the down vote - a comment as to why would be appreciated. And yes, you're correct (although it would be 50 in each table instead of 100 in a single table). I don't think that would be a situation that would happen very often, though. I think it's much more likely that a person will select a few people to share their comment with .. or a few people to NOT share their comment with.
    – Joishi Bodio
    Dec 9, 2015 at 17:19
  • i did not down vote ....consider a facebook post and like scenario ... a post can have 50 likes ...later on some one came an unlike the post... are they following the same db approach to relate post and like ...because a post can have 100000 of like ...!! ... just like that user and notes are like cross product ....
    – Pranav
    Dec 9, 2015 at 17:22
  • I wasn't saying you did. :) And that's what I am considering ... Facebook's model is, of course, way more complex as they have a sharing scheme set up and then your posts are assigned a sharing scheme (which then determines who is allowed to see stuff) ... that's leagues beyond what you are proposing here.. But just a simple "who can see what" can be accomplished by a "whitelist" and a "blacklist" (which is essentially what my answer is saying .. the "allowed" table = whitelist .. the "not allowed" = blacklist)
    – Joishi Bodio
    Dec 9, 2015 at 17:37
  • I can't say I know what Facebook/Youtube/etc do in terms of likes/dislikes.. I imagine they have a like/dislike COUNTER on each post ... and then users have a like/dislike table that contains what they have likes/disliked, but that's only a guess. Example, you view video "X" on Youtube. It says it has 1m likes and 20k dislikes .. that is information associated with VIDEO "X" (2 ints within table row) .. it ALSO says that "you have Liked this" .. that's information associated with YOU (1 row in a user/like table). Again ... that is a GUESS. heh
    – Joishi Bodio
    Dec 9, 2015 at 17:42

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.