CODEWITHSUNDEEP
phpmysqlsqldatabasedatabase-design
Jamal Salim
Jamal Salim

Reputation: 63

Database design for a document sharing system

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

Upvotes: 1

Views: 2170

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76426

Imagine the case when instead of a record per permission, having as many records as many permissions, you would have values like:

id1,id2,...,idn

Now, let's see a few problems:

Number of privileges

With the change you intend to accomplish, you would have to count the number of commas (and add 1) for each permissions, which would cause you a LOT of headache. Currently you can just count the number of records and you can group the results as you want.

Adding a new permission to a document

Suppose you have to add an id to the permissions list of a document. In thi case you will have to search for the permission record of the document. If not found, then you will need to insert a record and ensure that your id is put there. If found, then you will have to update the same record. Currently it's just an insert if the permission does not exist, often you already have the information and do not even need to query.

Removing a permission from a document

To achieve this with the suggested schema you will need to find the permission record of the document and check whether it contains at least a comma. If so, then you will need to split the values and construct a character sequence which does not contain the id you want to remove and update the record and to delete the record if there was no comma. Currently you just have to remove a record.

Finding the documents a user has permission to

With your suggested schema you will need to do some pretty slow string operations, like 

like '%,<theid>,%'

and ensure that all the strings start and end with comma for this query, but without actually modifying the data, so you will need to concatenate the actual value with a comma prefix and a comma suffix, making your code underperformant and highly difficult to read and maintain. Currently you can easily query for such values.

1NF

In short, your suggestion would worsen your design. You will need to ask yourself whether you really have a problem with your current schema and if so, what it is. If you have serious problems with performance, you will need to make sure you correctly identify the problem with performance and if this is the problem of performancee, then improve the schema, possibly by adding indexes to some columns. Your suggestion would deviate from 1NF and it is a bad idea.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

First, your query should be:

SELECT d.document_title
FROM documents d JOIN
     permission p
     ON d.document_id = p.document_id 
WHERE p.user_id = 12;

Use proper JOIN syntax. And presumably user_id is a number, so the comparison should be to a number, not a string.

Second, as you describe documents, they would appear to have owners. So, documents should have something like owner_user_id. Or, perhaps ownership is a type of permission.

Third, permissions are usually of different types -- read, write, and delete come to mind. So, you probably want permission types.

Fourth, have 200 rows for 200 users is not a problem a priori. You certainly don't want to fix it by storing data contrary to relational database practices -- that is, munging integers into strings to store multiple values in a single column.

Instead, you might want to consider introducing some concept of "groups" so users can join a group and the group has access to the documents.

Upvotes: 3

Related Questions