> Just a quick question I came up with...
>
> I have a table where you can put comments on something and at every
> comment
> I can upload multiple files. Something like this:
>
> table_comments (id, title, comment, ...)
> table_comment_files (id, comment_id, filename, path,...)
>
> Let's say I build my constraints in my database. If I delete a
> comment, it
> will also delete the records in the files table. But, then, it also
> needs to
> delete the files from the server.
> The only way I can do it, before I delete the comment, searching for
> files
> and delete them from the server, hard coded. Or is there another way?
> 'Cause, let's say, if my comment is on an article. And I delete the
> article,
> then I also have to look there if there are files on the comments,
> on that
> article I want to delete...
There are several solutions. Each has advantages and disadvantages.
Choose the solution based on your situation.
1. You could delete files from the server before you delete the rows
from the database. In the cascading delete scenario you describe, you
do have to know how your own database works and predict what dependent
tables are going to be affected.
2. You could store files in a BLOB or TEXT column in the database, and
then it would be deleted automatically with the row to which it
belongs. But adding files to the database may make it a lot larger.
3. You could write a trigger on the dependent table that calls a UDF
to delete files. But I wouldn't recommend writing a UDF that can
affect the filesystem. That could have dire consequences if your
application gets hacked through SQL injection attacks.
4. You could delete the database row and leave the files orphaned, and
clean them up periodically by comparing the filenames that are still
listed in the database to the list of files on disk. Any files that
are unreferenced may be deleted.
5. Similar to 4, but you add another table to list filenames and
paths, and then your table_comments_files is only a pair of foreign
keys to associate a comment with one or more files. This makes it
easier (using OUTER JOIN) to do the periodic check for unreferenced
files.
Regards,
Bill Karwin
没有评论:
发表评论