CODEWITHSUNDEEP
mysqlphpmyadmin
SGeri
SGeri

Reputation: 29

How to store multiple id's in one row?

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.

Upvotes: 0

Views: 2481

Answers (1)

Santosh
Santosh

Reputation: 886

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

Upvotes: 3

Related Questions