0

I'm creating a notes app with cloud syncing. The cloud is a MySQL server. There is a user table, which have name, password and id rows, and a note database with id, note, labels, creator_id and shared_id. The id from the user table is the same as creator_id, and the problem is with the shared_id.

The users should be able to share notes with multiple other users. For example, user 1 can share his note with user 2 and user 3, but I can't figure out how to store multiple id's in the shared_id row.

_________   ______
user        note
_________   ______
id    ----- creator_id
name        shared_id   <--(array of user id's)
password    note
            labels
            id

I'm thinking of creating a group table, with a group id and the user's id (called as user_id), but the problem still exist, there would be more than 1 id in the user_id row.

________
group
________
id       <--(id of the group, this would be used at the shared_id)
user_id  <--(array of user id's from user table)

Many thanks in advance for your help.

Example database:

user
id  name          password
0   "test user"   123456
1   "shared with" 123456
2   "another user"123456

note
creator_id   note                       labels        id   shared_id
0            "Something I will forget"  "home, shop"  0    1, 2

Creator_id is the id of the user who created the note, note and label is the note, id is the id of the note (not connected to users) and shared_id should be the list of users who can watch this note.

The group table would look like this:

group           note
id   user_id    shared_id
3    1, 2       3
|               |
-----------------

Group's id and note's shared_id would be the same, user_id's would be id's from the user table.

SGeri
  • 29
  • 9
  • You should almost never store many ids in the same row. – Alex Mar 29 '19 at 13:42
  • 2
    You should create a table `shared` with columns note_id (referencing note.id) and user_id (the id of the user that the creator of the note shares it with). In this table you add 1 row for each user. – forpas Mar 29 '19 at 13:50
  • @forpas That's a good idea! Thank you! – SGeri Mar 29 '19 at 14:00
  • You should read my answer to [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Mar 29 '19 at 14:17

1 Answers1

2

As @forpas suggest in comment,

One of the best approach is to create another table called "shared_notes" with two fields i.e. user_id and notes_id. Where user_id is id from user table with whom a note is shared and notes_id is note id.

Then you can store multiple records, for example if user u0 shares note n0 with u1 and u2 then table will be,

shared_notes
------------
user_id      notes_id
u1           n0
u2           n0
Santosh
  • 874
  • 11
  • 21