Can we really omit database table for image path?
Of course you can but (see later) I'd consider this solution only for very small databases.
Is it ok to design a system in this way?
IMO it's not unless you're working with a very small amount of data, with pretty fixed requirements and easy logic around them.
What are the possible issues associated with this kind of design?
Here long text comes. Common issues I may think about are (at least) these:
- If you use a technology like FILESTREAM (or equivalent, according to your environment) tricky operations like delete and rename will be handled correctly by database engine. Doing by hand (with or without file path stored in a column) will make your code weaker unless you carefully design such operations (primary because of concurrency). What's easier to write with a path column is the script to find orphans. This may (or not) be an issue (as you said an user is forever) but it's something you have to consider.
- For security reasons you may setup your storage space to be read/write but not to be browsed. If you don't have a path column you need to make your storage browseable.
- If you need paths in your queries then you need that column (for example to count how many images each user has, average number of images per user or to prepare a list without accessing disk).
- With such column you'll be safer also from concurrency point of view. If one user is accessing the list (for example reading files) someone else may decide to delete them. If access is coordinated with a lock at table/record level then it'll work naturally but if they're unrelated then this has to be done in your code (don't underestimate this).
- If space to store images increases then you can easily add new disks, this it's easy to handle if you don't have to search for proper path but it's stored inside DB. You may also have different disks (with different speeds) for different users and you won't pollute your code with such details (image browser will ask paths, it's always unaware of such business stuff). Of course you may do it in code but then you'll put such logic everywhere you need to gather such list, point here is code maintainability.
- Path stored in a database column can be a virtual path (as a plus for what I said in point 5) and it may be resolved with data from other tables. Everything can be done in SQL and your code will just see a path. If DB has not such information then this calculation has to be done in code (and it'll be harder to change because it's spanned). Again point is maintainability.
- To reuse an image for multiple users you can simply duplicate its path (assuming it's read-only).
- Images can be made public (to be visible to many users). This information has to stay in DB but if you don't have a file path then it'll be harder.
- Images may not even be stored locally but in another server (through HTTP). It's an easy change if you have paths in DB but it's a pain if such code is in your presentation.
Please note that points from 5 to 9 imply some kind of business logic. It may be in the database layer (in SQL or with external code - for example .NET for MS SQL Server) or in your Data Access Layer outside DB. The true difference here is that with such column you have flexibility to put such logic where you prefer (according to your specific architecture and also move it up if you increase layers). If you don't have such column then you can't simply put such logic in DB (and this is especially bad for small 2 tiers applications).
Here a simple requirement that will convince me to adopt that path column:
Store images bigger than 10 MB on path \\BIGDISK\IMAGES.
Images smaller than 10 MB are on D:\IMAGES.
Premium users always stores on D:\IMAGES.
Trial users will store on remote server http://example.com/very_slow_storage).
Of course you can do it in your code but it's easier in SQL and it's (more important) in one well-known point, not spanned across your presentation logic (to insert images, to upload them, to get the list, to calculate statistics, to build a preview...). Even more important: it's easy to change, no need to update (and to deploy/merge) configuration files or (even worse) with code changes.
If you answer "I don't care" for all these points and you're not planning any change in future then IMO you can drop such column in your database.
Of course it's a design change that you may reconsider every time you have a new requirement (or when you find your presentation code is aware of details it should really ignore - such as where an image should be stored).
