I am storing images in a SQL database , right now I have images being stored in separate tables depending on the object the images belong to, is there any reason ( performance etc..) why I should keep it this way and not store all images in the same table?
A:
Normalization! If you want to follow a normalized schema then you should probably have a media table that contains all of your images, then you can have a table with the relationships from your objects to the media.
This is how I have done it, and it works well for me.
Dustin Laine
2010-05-27 21:51:48
A:
For 95% of the time a single table will work just fine (See Adonis L’s answer) I use this pattern often.
However if you had a lot of images and the activity is evenly(ish) split over say 5 groups and you were running into an IO bottle neck then you might gain some speed by having the 5 tables on 5 different file groups spread over different hard drive spindles. You are then effectively spreading the load over multiple heads and making a crude form of load balancing/partitioning
Of course this is only an example but worth thinking about if your data is structured that way
Kevin Ross
2010-05-28 07:17:22
The better way to slit that IO load would to be use partitioning, rather than manually splitting table and using business logic to use appropriate table.
Dustin Laine
2010-05-28 18:48:35
@Durilai, Fair point however not all of us have a full enterprise edition kicking around so have to "make do and mend" with plans like the above
Kevin Ross
2010-06-01 07:12:53