CODEWITHSUNDEEP
phpmysqldatabase-designamazon-s3
KillABug
KillABug

Reputation: 1414

Database design for a S3 based file storage application

I am working on a file storage application and I am using PHP and MySQL as my tools. My use case is user can share multiple files with multiple users using the app. The sender will send URLs via email and the receiver has to click on the link and login to see the file. So when a user shares a file I want to make an entry to the database.

 id|filename|shared_with|shared_by|shared_on|shared_url|url_expiration

Now, the above is my currently thought database structure. But in this case I will have to store multiple multiple values if the same file is shared with multiple users, which I believe is not a good way to do. Also, storing comma(,) separated values is not a good idea.

I gave a thought to Document database like Mongo DB (just because dropbox uses it and handles key value pair data well). But as MySQL can handle a decent amount of records and NOSQL can be a potential solution for bigdata now, I am not sure which would be the right way to go about this use case.

I would like the experts to throw some light on it. I am using Amazon S3 for storing files.

Upvotes: 0

Views: 972

Answers (1)

Benny Hill
Benny Hill

Reputation: 6240

Here's a very basic design to get you started...

You need a table to store your file information:

files
    id              unsigned int(P)
    owner_id        unsigned int(F users.id)
    name            varchar(255)

+----+----------+----------+
| id | owner_id | name     |
+----+----------+----------+
|  1 |        1 | File A   |
|  2 |        1 | File B   |
|  3 |        1 | File C   |
|  4 |        2 | File 123 |
| .. | ........ | ........ |
+----+----------+----------+

You need a table to store information about what files where shared with whom. In my example data you see bob shared File A with mary and jim, then he shared File B with mary.

shares
    id              unsigned int(P)
    file_id         unsigned int(F files.id)
    shared_with     unsigned int(F user.id)
    shared          datetime
    url             varchar(255)
    url_expires     datetime

+----+---------+-------------+---------------------+-------+---------------------+
| id | file_id | shared_with | shared              | url   | url_expires         |
+----+---------+-------------+---------------------+-------+---------------------+
|  1 |       1 |           2 | 2014-01-06 08:00:00 | <url> | 2014-01-07 08:00:00 |
|  2 |       1 |           3 | 2014-01-06 08:00:00 | <url> | 2014-01-07 08:00:00 |
|  3 |       2 |           2 | 2014-01-06 08:15:32 | <url> | 2014-01-07 08:15:32 |
| .. | ....... | ........... | ................... | ..... | ................... |
+----+---------+-------------+---------------------+-------+---------------------+

And finally you need a table to store user information.

users
    id              unsigned int(P)
    username        varchar(32)
    password        varbinary(255)
    ...

+----+----------+----------+-----+
| id | username | password | ... |
+----+----------+----------+-----+
|  1 | bob      | ******** | ... |
|  2 | mary     | ******** | ... |
|  3 | jim      | ******** | ... |
| .. | ........ | ........ | ... |
+----+----------+----------+-----+

Upvotes: 1

Related Questions