File Sharing System ERD (With Database Structure)

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

 

 

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.

 

 

 

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.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• Simple File Sharing In PHP MYSQL – Code Boxx