I'm trying to figure out if my approach for document sharing system is good (I'm using PHP & SQL). I think I've built a bad DB design.
To put it simple, the concept is to create documents, share it with others, get updates and feedback using comment system.
Users can share their documents with any other users on the network (intranet). Users can access the documents which other users shared with them and leave comments on it.
This my Database Schema
(removed some of the columns such as: created_time, user_ip...etc)
- documents:
----------------------
-- document_id
-- document_title
-- document_content
-- document_category
- comments:
----------------------
-- comment_id
-- comment_content
-- document_id
-- user_id
- users:
----------------------
-- user_id
-- user_name
-- user_type
-- user_password
- permissions:
----------------------
-- document_id
-- user_id
After the user log into the system, the PHP will view all the documents that he can access using the this SQL Query:
SELECT d.document_title
FROM documents AS d, permission AS p
WHERE d.document_id = p.document_id AND p.user_id = '12'
The above query is also used to grant the access of the document
Now imagine the number of rows created for a single document shared with 200 users across the network, it will be 201 rows in the permission table!! which I think is a bad approach
I'm trying to do a different approach which is changing the permission table to this:
- permissions:
----------------------
-- document_id
-- users_ids
This will allow me to save users ids in one column and one row per document. but I'm not sure if this is the proper way to do it and honestly I can't see how I will make it using PHP and SQL
Please advise me and give me your feedback
Thanks