Mongodb schema design advice

database-designmongodb

I have a users index which has fields like id, email, password, etc. A user can see all other users except the ones who had blocked him. I want to design a database schema for this problem.

So I'm using MongoDB and I have these two tables.

Users

id   email   password
01   a@b.co  123
02   b@c.co  XYZ

now, I need a way to store the block relations information. If x has blocked y then y can't see x on the user's list.

If I add an array in user y which contains all user id who has blocked them so would it be a good solution? or is there any other alternative?

Users

id   email   password   blockedBy
01   a@b.co  123        []
02   b@c.co  XYZ        [01, 07]

now, I'll show all the users to user 02 except 01 and 07.

Best Answer

While this would work, IMHO, this isn't ideal because of the odd permissions / security setup that would be required. In your setup, user 1 would require access to user 2's record. Similarly, user 2 could potentially change their list of blocking users.

You could of course, setup a security scheme that would work, but, to me, it just seems awkward and not intuitive, which means it will become brittle and prone to breaking in the future as your schema evolves.

Generally speaking, it's much easier conceptually to assume that a user has permission to alter his own record, and no one else's (set aside group permissions for now). Creating a security scheme that safely grants access for one user to alter another user's record can become tricky and prone to errors.

I think it would be simpler to create a separate collection to hold the blocked relation. Something as simple as this could work: {blocker: 01, blockee: 02}. If you put an index, you won't lose performance over storing everything in a single collection.

Related Solutions

Mongodb – Schema design for privacy settings in MongoDB

It's important to remember that since there are multiple ways to design schema in MongoDB for a given set of data, it's critical to consider the types of reads and writes you will be making from your application.

Assuming that you will be querying for a subset of documents that a given user can see, it's likely that you will want to structure the document collection in a way that will give you the ability to query for all "visible" documents in a single query.

Document structure such as:

{ _id: ...,
  public: true,
  groups: ['group1', 'group2',...],
  users: ['user1','user2',...],
}

You could now query for all documents visible to user X if you have a list of groups user X belongs to by querying:

db.documents.find( { $or : [
     { public : true },
     { users  : X },
     { groups : { $in : [list-of-X's-groups] } }
] } )

Mysql – Need advice for table design for multi-user access

I'd try creating a group and user_group table. Then change map_user_id to map_group_id and your group all have access to manage that item.

Eg.

+------------------+------------------+
| primary_group_id | group_name       |
+------------------+------------------+
|             1    | Edit Stereos     |
+------------------+------------------+

+---------------+---------------+---------------+
| user_group_id | user_id       | group_id      |
+---------------+---------------+---------------+
|          1    | 1             | 1             |
|          2    | 4             | 1             |
+---------------+---------------+---------------+

+--------------+-------------+----------------+------------+-----------+
| map_group_id | map_item_id | unique_item_id | item_owner | privilege |
+--------------+-------------+----------------+------------+-----------+
|            1 |       100   |              1 |          1 |      NULL |

Best Answer

Related Solutions

Mongodb – Schema design for privacy settings in MongoDB

Mysql – Need advice for table design for multi-user access

Related Question