Menu
I am developing a website where a single image uploaded to the site may be used by multiple business modules like Food Item, Dish, Gallery or whatever.
I have a main image table that stores references to actual images on the file system.
Now, each record on this table can be referenced by item, dish or gallery tables.
My question is, should all these references be stored on a single table or should I maintain a separate table like ItemImage, DishImage?
Problem with single table is I fear this table will be bombarded with high hits eventually increasing the response time. Problem with multiple tables is I have to keep adding more tables as the module count increase on the business front.
If I say performance is top priority to my site, whats the best approach?
SQL Server 2012, .Net 4.5, MVC 4.
Thanks
916
To insert images into a database, the database must support images. Images are stored in binary in a table cell. The data type for the cell is a binary large object (BLOB), which is a new SQL type in SQL3 for storing binary data.
The answer is "It depends." Certainly it would depend upon the database server and its approach to blob storage. It also depends on the type of data being stored in blobs, as well as how that data is to be accessed. Smaller sized files can be efficiently stored and delivered using the database as the storage mechanism.
Generally databases are best for data and the file system is best for files. It depends what you're planning to do with the image though. If you're storing images for a web page then it's best to store them as a file on the server. The web server will very quickly find an image file and send it to a visitor.
SQL Server allows storing files. In this article, we learned how to insert a single image file into a SQL Server table using T-SQL.
I would recommend using a single table. As mentioned by @Brandon, if indexed properly there should be no performance problems.
I would also recommend that you store only a relative path from a common root folder (as recommended by @SpectralGhost). The common root folder can then be a configuration setting allowing the operations team to change where the files are stored without needing wholesale updates to the database.
File system performance may become an issue if you have thousands of files in a single folder. I recommend making a couple of levels of sub-folders for more of a tree structure. For example, if the file name is ABCDE.jpg then using a path like A/B/ABCDE.jpg will split the files over thousands of sub-folders.
I also recommend that you use a GUID or similar as the base file name for the uploaded files to avoid any naming conflicts (and use the dashless version of the GUID to save 4 characters). If you need the original file name, put that in another column in the image table. Storing other image metadata, such as the width, height and Content-Type (e.g. "image/jpeg") is a good idea too.
Categorizing each image using a keyword tagging system is probably the best idea for logically grouping the images. Each image could then theoretically be assigned to any number of categories (including zero). This would require a tag list table, and a separate mapping table containing (tag_id, image_id) pairs.
160
This is what indexes exist for. Put them all in one table, and as long as it's indexed performance won't be an issue for this scenario.
Think about if you had a book with 1,000,000 pages in it. Even for you as a human being, it wouldn't take all that long to find any given page number... so long as the pages are in order. And the difference in time for you to find a page in a 100,000,000 page book really wouldn't be significantly longer.
Now if you were doing complex reporting on the data it might be different, but this is just a lookup table.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
