Welcome to a sharing of an ERD for a file sharing system. So you have picked up a project for a file sharing system? Or maybe you are curious about what goes on behind one? Well, here is a sharing of my own simple version – Read on!
TABLE OF CONTENTS
ER DIAGRAM
First, here is the entire ER diagram and a quick overview of the system.
FILE SHARING SYSTEM ERD
To keep things simple, I have grouped this system into 3 sections.
- Users – Don’t think this needs an explanation…
- Files – Files that the users have uploaded.
- Share – Which file is shared with whom.
FOR REFERENCE ONLY
My developer senses are tingling, I can feel the “experts” banging on their keyboards. “Dumb, worst, stupid”, “not accurate”, “not complicated enough”. By any means, this is only a simple example for reference.
- If you want to add folders, go ahead and do so yourself.
- If you don’t want to track the “file history”, go ahead and remove it.
- If you want to further control the “number of downloads”, go ahead and do the necessary changes.
- If you have specific “storage and sharing” quota or even user levels, go ahead and add it yourself.
Yes, you are building your own system. You have to decide what is “not stupid” and what “makes sense” for yourself.
TABLE STRUCTURE
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary key. |
user_name | VARCHAR | User’s full name. |
user_email | VARCHAR | User’s email. Set this to unique to prevent duplicate registrations. |
user_password | VARCHAR | Login password. |
user_level | VARCHAR | Up to you to decide – “Admin”, “Staff”, “User”, “VIP”, etc… |
Well, this table should be “Captain Obvious”. But depending on how you want to run the system, the “user level” may vary greatly. For example:
- Different user levels have different storage/sharing limits.
- Or create a separate table, and allow “dynamic storage/sharing” upgrades for $X every Y GB.
- Maybe even both – The base user level offers a certain limit, and users can purchase upgrades.
PART 2) FILES
2A) FILES LIST
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary and foreign key. |
file_name | VARCHAR | Primary key, the file name. |
file_size | INTEGER | File size, in bytes. |
file_type | VARCHAR | MIME type. |
To keep track of “which file belongs to which user”. Now, I am not going to go into the exact details of “how to store the files” – That is not a part of this guide.
2B) FILES ACCESS HISTORY
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary and foreign key. |
file_name | VARCHAR | Primary and foreign key. |
history_date | DATE TIME | Date and time of the “file movement”. |
history_type | VARCHAR | Up to you to decide – Upload, download, rename, move, share, etc… |
history_notes | VARCHAR | Notes, if any. |
history_user | INTEGER | Foreign key, the user who accessed the file. |
history_ip | VARCHAR | The user’s IP address. |
The system will still work without this table, but it’s here for the sake of security. If you want to restrict the number of times a file can be accessed, this is also a good place to check.
PART 3) FILE SHARE
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary and foreign key. |
file_name | VARCHAR | Primary and foreign key. |
share_with | INTEGER | Foreign key, the file is shared with this user ID. |
share_link | VARCHAR | Anyone with this unique link can access the shared file. |
share_permission | VARCHAR | Up to you to decide – Read, write, download, read once, delete, share, etc… |
share_password | VARCHAR | Password to access this file, if any. |
Finally, don’t be fooled by a seemingly simple “single table”. File share can be very complicated –
- Only sharing with registered users only?
- Allow public file share? Anyone with the link can access the file?
- Password lock the shared file?
- Control the number of times it can be accessed or downloaded?
- Allow someone else to make a copy of the shared file?
- Set the permissions for the shared file?
- Automatically expire the shared file after a certain time or times of download?
Yep… These are things that you will have to think deep and decide.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
- Simple File Sharing In PHP MYSQL – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!