CODEWITHSUNDEEP
mysqldatabase-design
Tek
Tek

Reputation: 499

Same data for many enitites in database

a while ago I implemented a gdrive/dropbox-like app with a globally predefined directory structure (not modifiable) that each user could use, but was not limited to (meaning: was also able to add and manage custom folders).

The static directory structure is the reason for this post, because I‘m not satisfied with the current handling mechanism and would be really happy, if you could give me a good advice how I could improve it/change this for the better.

At the moment I use a MySQL database, which has a table 'folders', that (surprise, surprise) contains all the folders (predefined and custom). Therefore it has fields for folder name, owner and parent folder.

Because the predefined structure is pretty huge, I didn‘t want to add it for every user to the table, so I seeded the folder table with only one instance of this structure and left the "owner" field set to NULL. Therefore to find all the folders for a user I just need to query for the ones that have this specific user as a owner, or are not owned by anyone.

This approach works pretty good so far, but has some major drawbacks when it comes to per-user attributes for the folders, e.g. I‘d like to show the document count in each directory - including the subdirectories - which is done at the moment by using a really slow recursive query every time. This could be handled much better if I would just have a per-user-folder structure (for example by adding an additional 'document count' field, which could be updated by using query hooks every time something is happening to a document in the folder structure).

What do you think about this design choice? Should I keep it that way and just add an additional table that contains the per-user-folder attributes (e.g. structured like user_id, folder_id, document_count, last_modified, [any other attribute I can come up with])? Would it be a better approach to handle the folders directly on the system (by utilising system commands) and keep them out of the database? Or do you have any other idea (maybe a better suited database?) how this could be managed in a much more convenient way.

Thanks for your help! :-)

Upvotes: 0

Views: 33

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

If I understand correctly, you are storing all files in the database. So you probably have a table files containing files (binary) along with their folder ID. So after all folders are just names to enable the user to structure their data and ease access. But this also means, you don't have to make this a hierarchic structure in the database you must scan with recursive queries.

Say, there is a fixed folder A and a fixed folder B inside A. The user added three folders. These are the user's records in the folders table:

id    folder_path   user_id
1     A             1         (every user has this)
2     A/B           1         (every user has this)
3     A/B/C         1
4     D             1
5     D/E           1

If the user opens their storage, they are shown all main folders (those without a dash in folder_path): A and D. If the user opens one of the folders, say A, you show all folders inside (i.e. all starting with A/ and having one dash in folder_path): A/B in our case, plus all files with folder_id 1. If the user renames B to F then change every folder_path that starts with A/B to start with A/F instead. If the user moves F to inside E then change every folder_path that starts with A/B/F to start with D/E/F instead.

Counting files is just as easy:

select count(*)
from files
where folder_id in (select id from folders where folder_path like 'A/B%');

All these are simple operations, because nothing has to move actually, you'd always only look up folders the path of which start with a certain string or you'd change the start of folder paths.

Upvotes: 1

Related Questions