Best to use: mysql column or file check to detect image?

I always have a dilema: For a record, I can use column images (tinyint) that is true if there is image for that record or false if there is not.

I can also not put that information in the database and in my code I "snoop" with disk filesystem check if the image exists.

Both give the same results of course. Having in database means maintaining the image state separately from the real image on disk, means harder to program and more prone to errors (disk image is not there any more, in the database the re开发者_Python百科cord has true on column image).

So I use disk check usually. But it occured to me, maybe this has a hard penalty on the disk access. I know database check must be faster, I have to get the record off the database anyway. But is looking for image using filesystem as bad as it occured to me or not?

Pros for storing it in the database:

• You can create queries that answer questions like "Give me all objects that have no image"
• Its much faster as you do not have to touch the disk

I can see no pros for only storing it on disk. Use a pattern with a "single place of responsibility", meaning that only one place in your code saves, updates, deletes images. In this place you update the file and the database. Done this way, its not very prone for errors.

Put the other way round: if this makes your application prone for errors, you should check your architecture.

In my opinion checking on the disk is wasteful and a bit of bad practice. The DB should hold this, because:

• It is much faster.
• A lookup on the disk which does not retrieve the wanted record should be seen as an error (which can also be taken care of elegantly, of course).

Maintaining the column shouldn't be too much fuss, as you maintain the name of the file anyway, right? So when the filename is updated this column should also be updated accordingly.

Are the images more likely to be there or more likely not to be there? If you're seeing if the file exists will you always use the file?

If the answer to both is "yes", then by all means, use the disk. If you have reason to believe that it will be there the overwhelming majority of the time, then use @file and handle a FALSE return (especially since file_exists can have cross-platform implementation issues). I'd consider this to be more than adequate practice.

If not, then you need to look at your program flow:

Ask database -- does file exist?

• yes = fetch file.
  • If file is not really needed do something assuming file exists
  • else fetch file => has the file fetch succeeded? (you're doing this check even with the DB query)
    • yes = do something with file
    • no = update db and do something without file.
• no = do something without file. (when will file existence be updated? cron?)

If you plan on using the file, you have to check for the file if the DB says its there which means that the only time that you can optimize with use of a DB is when the file itself is not needed.