July 13, 2009 at 2:28 pm
hi
We have taken the design decision to store images in our database. This would be our 1st "image" table. I need help on how best to design this table so that it fulfills all entity and referential integrity with our current database schema but also is easy maintainable from a DBA perspective (backups, restores, etc). there are 2 train of thoughts that i have at the moment and i need some help on which is best.
The table size could grow to more than 300000 rows of 30k each for each image. We are intially storing photos of individuals but there might be in the future the need to store any other type of images (like for organisations, parties, etc). Our current database design is very much an party-individual-role-organisation generalization type schema. We have an individual table, an organisation table, and then a linking/relationship table which links these 2 with a particular role.
So the 2 options I have are:
1) Create a generalized image table that will just store images and all the general properties for an image and then link this to the individual table or organisation table through sub-type linking tables, so something like:
t_Image:
ImageID int
ImageData varbinary(max)
Width smallint
Height smallint
t_IndividualImage
IndividualImageID int identity(1,1)
IndividualID int --foreign key to t_individual
ImageID int --foreign key to t_Image
t_OrgImage
OrgImageID int identity(1,1)
OrganisationID int --foreign key to t_organisation
ImageID int --foreign key to t_Image
OR
2) Create specific image tables 1 for each requirement or entity in the future. So for example we will have an IndividualImage table and OrganisationImage table each able to store images.
t_IndividualImage
IndividualImageID int identity(1,1)
IndividualID int --foreign key to t_individual
Photo varbinary(max)
Width smallint
Height smallint
t_OrganisationImage
OrganisationImageID int identity(1,1)
OrganisationID int --foreign key to t_organisation
Picture varbinary(max)
Width smallint
Height smallint
Both of the above options re image tables will be stored on a separate file/filegroups to make maintenance more acceptable with regards to growth, capacity planning, backups, restores (to dev environments for example).
There are also thoughts to maybe separate the image table totally out and store in another database but then we lose the tightly-coupled referential integrity provided by the foreign key constraint.
So which of the 2 design options are better to use going forward as we might add more image-types/tables going forward. Which is more correct?
July 13, 2009 at 2:55 pm
I'm going to assume this is SQL 2005. If you're actually using 2008, then take a look at the FileStream data type.
For 2000/2005, you're almost certainly going to be better off storing the files outside the database and storing the file paths in a varchar column. It's not as good as filestream in 2008, but it's a lot easier to manage and deal with than storing them as varbinary in the tables.
There are a lot of reasons for this, but the main one is performance. Once you have more than a few images in there, and more than a couple of concurrent users, you'll find that having the file system serve up your image files is a LOT faster than storing them in the database.
Backup management and such is another good reason to do it this way. It's easy to back up the images once and then use the Archive flag to know which ones you need to include in later backups, if they are stored in an NTFS partition. Storing them in the database makes it a lot harder to manage.
If, after reading the above, you still insist on storing them in the tables, then I recommend not only splitting the table into a separate filegroup, but partitioning the table by age, so you can manage backups with that much more granularity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 13, 2009 at 5:04 pm
GSquared (7/13/2009)
I'm going to assume this is SQL 2005. If you're actually using 2008, then take a look at the FileStream data type...
I second what Gus says, and I'll take it one step further, if you are not currently using SQL Server 2008, then I strongly recommend that you consider upgrading to it for just this reason.
It really does give you the best of both worlds: all of the advantages of storing the images in the database that you mentioned, plus all of the performance advantages that Gus pointed out (plus more on both counts). Especially if this is new development, it's such a big win that's it's hard to imagine why anyone wouldn't do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 14, 2009 at 1:56 am
Yes we are on sql 2005 Standard Edition so we cannot partition and we cannot use filestream. I know that filestream is the better option but we are still a bit away from upgrading to SQL 2008. There is a whitepaper from Microsoft research that concludes that storing images of <256k is actually better stored in the database, so hence that's why we are going with this approach.
So I need more an answer on which table design to use as pointed out in my original post - option 1 or 2?
July 14, 2009 at 6:50 am
Given those constraints, I'd go with one image table and two details tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 20, 2009 at 12:12 pm
Agreed on both parts that it'd probably be better in the long run to store files outside of the DB, and that if you HAVE to store in the DB that the 1 table and 2 "linking" tables. That way, I think it's much more scalable if you decided to A) allow people to have more than one file, and B) decide to link other entities to pictures or files.
That being said, I'd still recommend going with storing outside of the DB, because if your rules change, and you need to allow for larger files, then the benefit of storing the smaller ones in the DB is gone. I haven't messed with storing files in SQL Server 2005, but I know from other DBs that it's very easy for files stored in a table to get corrupted, and for there to be issues when saving and loading the data. Not saying that that's how SQL Server 2005 is, but if they specifically made a change in 2008 to allow for better file storage, then that probably means that there was something significantly wrong with 2005.
That all being said, do people with experience with this FileStream datatype in 2008 think that it's significantly better to use that than storing outside of the DB? And if so, is it fairly easy to set up a process to load files that are stored outside into a table if someone upgrades? I'm not trying to hyjack the thread, but I thought that planning ahead might help you decided what to do as well.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply