Hello,
I have not worked with databases a lot so I'm having difficulty modelling how to create the following database.
The requirement for my project is:-
Users (who have a name and email address).
Each user can have multiple files.
Each file can be shared with multiple other users
Files can be tagged with categories.
This way whenever a file was added/shared it would be added to the file database with the id of the user who uploaded it/id of user who it got shared with.
This model would mean whenever a file is shared a new copy of the same file is made in the database with new id, is there a more efficient way to do this?
Also how do I implement the categories feature? Is there a way to dynamically create text fields in the database for each new category?
I realize that I should probably try to help you figure it out rather than just giving you a solution... but I'm not the best at that, so I'll just explain what I would do to the best of my ability.
I'd probably do a few things to spread out the information (also called normalization). Like Kyle mentioned, I'd use a third Join table. It's just a table with IDs in it to map one object to another - in this case, Users to Files.
So I'd have a User table with an user_id, name, and email. Then a File table with file_id, name. Then a UserFile table which has user_id, and file_id. Now, whenever a File is shared with a User, that user's id is inserted into the UserFile table with the corresponding file id.
Categories would depend on the requirements... First off though, because many can be assigned to a File, I'd create a new table for them. Table Category, category_id, and category_text.
If Categories are assigned to every File, regardless of which User tags it, the Category table would gain a foreign key, file_id. Now I have a File which any user can see, and that file_id can be used to retrieve all the Categories assigned to it.
If Categories are assigned to a File on a per user basis (i.e. You sent me a Text file and have tagged it as Text, but I tag it as Junk. I don't see that you tagged it as Text, I only see my tag for it). Instead, the category needs to be applied to the association between the user and file, i.e. the UserFile table. Now, this could be done by adding two foreign keys to the Category table - e.g. file_id and user_id. But, I personally find it more elegant to re-use the objects I already have available to me - otherwise why did I create them? So, I'll add a unique user_file_id key to the UserFile table (in truth I would have done this from the beginning regardless of the Categories table because I don't like multiple primary keys when I can avoid it). Now, my Category table can gain a foreign key, user_file_id which directly references the UserFile which has been tagged!
Make sense? Over designed? <- I'm very guilty of doing this too often.
The winds of change are blowing; will you heed their call?
