here's what I thought when I read your post (in red):
Summary: I will allow users to upload following files - PDF/Videos/Photosplease dont tell me you're storing them in the database as blobs. All files uploaded by the user are shown on a profile page 1 user can have many files, store user_ID in the file table . All uploaded files can be searched by name 1:1 with file or tags many to many, now you need a tags table and FileTags table and file type1:1, maybe 1 to many if an excess of file types exists . I store file paths in database not the actual file. I wonder if he learned this the hard way also...
At moment i will store all files details in one mySQL table, myisam engine used for fulltext and tag searching and also faster to select data. can't cascade delete a user's files when the user is deleted... better handle gc on the application level
Say if their were over 10+ million records stored in this one table, the search would be eventually get slow. what are you searching against... the name? just use CI substrings? i am thinking of breaking the single table into three separate tables e.g a table for pdf/photos/videos in this case if the users want to search all file type, i do union all search. would this be a better approach ? [COLOR="Sienna]this is a terrible idea, use a byte field or bit field... why would an entirely new table have a faster index?[/COLOR]
the drawback would be if I add a new file type at later stage i would have to create a new table , for a example allow users to upload zip files. ... I think you're missing the idea of 1N,2N, and 3N all together
Here's what I would make it look like:
See how I cut a corner by using ENUM(), that's OK when you can say "the list of possibilities is FINAL, never will another type appear". case closed. Since that's not the case, we can add in NogDog's suggestion:
to be short sighted:
ENUM can cut corners for a 1:many relationship (get rid of 1 table).
SET can cut corners for a many:many relationship (get rid of 2 tables).
but these 1 or 2 tables could contain many more relationships that ENUM and SET will not capture, and you might end up having to rewrite a lot of the code base if you accidentally cut a corner you shouldn't have.
I think this is acceptable:
Now some thoughts about this:
I don't like having all of the FKs being stored in a myISAM table, but that's exactly what you're wanting to do. For the sake of full text search.
I suppose you could make the FileType PK the fileType VARCHAR(45) field instead, and change the FK_File2FileType constraint also. But it begs the question, why even bother making the table if the FK is the foreign PK and no other fields exist in the table. More specifically, why does anyone make a foreign key to a table with only 1 field.
I guess the answer is- it makes more sense, because once 64 file types (64 bits) exist, the alternative is a binary string (blob).
Moving it to a new table enforces having unique file types stored while allowing for easy access and more than 64 types. If the PK is in fact the name itself, you may never need to join on the table at all (ever) and all of this is actually just a formality to beat the 64 bit limit on integers. Just a thought. If you have more than 18446744073709551615 file types you're screwed.
My check is in the mail right?