What is the best database structure for shared objects?

Question

SQL

There is a user, he has a document and I want to share this document with another user (I want to take the document through api, not through a shared link).
Where is the best place to save this resolution? :
1. In the document record, add everyone who can view the document (but then it is difficult to find this record by the user with whom it was shared)
2. Create Another table (collection) where to store what, who and to whom gave the rights to the document. (and two implementation options):
- create a new entry for each user (but then there will be many entries for one document)
- add an array of users who can view the document (but then the user will have to search for the document a little longer)
3. Your solution
Thanks in advance

Answer 1

Use redis and its sets are sadd. Set and add all users for this document. The key itself will be the document ID, and the members will be the user IDs

127.0.0.1:6379> SADD doc:1234 "uid:1234"
(integer) 1
127.0.0.1:6379> SADD doc:1234 "uid:1235"
(integer) 1
127.0.0.1:6379> SADD doc:1234 "uid:1237"
(integer) 1
127.0.0.1:6379> SMEMBERS doc:1234
1) "uid:1237"
2) "uid:1235"
3) "uid:1234"
127.0.0.1:6379>

Well, as a bonus, put TTL on the key, for example, for 12 days. After 12 days, users will lose all rights to the document.

127.0.0.1:6379> EXPIRE doc:1234 1036800
(integer) 1
127.0.0.1:6379> ttl doc:1234
(integer) 1036789
127.0.0.1:6379> ttl doc:1234
(integer) 1036786
127.0.0.1:6379> ttl doc:1234
(integer) 1036784
127.0.0.1:6379>

You can, and vice versa, make the key the user, and the available documents the values. It can be twisted in some other way.

Answer 2

1. In the document record, add everyone who can view the document (but then it is difficult to find this record

Why is it difficult? An array with users who can - quick search by index.