2

I was wandering what's the best design for the database of an application in which I have to store lots of records with blobs associated (One to one) to them.

Is it better to use a separate table for blobs?

My application relies on MySQL and Hibernate.

mariosangiorgio
  • 5,520
  • 4
  • 32
  • 46

2 Answers2

3

Using a separate table would be better in the long rung, especially if you've got lots of blobs. The point is when you have them in a table together with other fields, this table will take longer to rebuild or change with all the blobs in them. This table would be much quicker when you just refer to the blob.

I googled for some support of this statement, and found this lengthy but interesting read: http://mysqldatabaseadministration.blogspot.com/2008/01/i-will-not-blob.html

kasimir
  • 1,506
  • 1
  • 20
  • 26
  • The article is a good read but it is discussing if the **images are to be stored in DB or in file system**. The question was to use one table or multiple tables. – ManuPK Nov 18 '11 at 13:24
  • It also discusses database impact. And the use of one or multiple tables: 'Unless the BLOB is kept in a separate table, say photo_blob, from the main table, say photo, you are going to make schema changes a nightmare for yourself, since you will be performing migrations on an additional TT terabytes.' – kasimir Nov 18 '11 at 13:29
  • +1. Thanks for the info. Even I have updated my answer with these info. – ManuPK Nov 18 '11 at 13:48
0

Your choice should depend on the amount of data and transactions. If the amount of BLOB data are less(say no of files are less than 10000) you can follow these steps other wise it may be a bottle neck as per this article.

Is it better to use a separate table for blobs?

Did you mean one table with all the columns as BLOB type.I dont think it is a good idea.

What to do then?

BLOB is one of the many data types available in SQL. Your data base design should not be depending on datatype you use. Say you want to store User details including the image of the user. I feel there should a column in User table usrImage to be store the image with type BLOB. It does't really matter if I use BLOB or not I would continue to have a User table.

BLOB are similar to any other datatype, So attach them to where ever they fit in your DB design.

ManuPK
  • 11,623
  • 10
  • 57
  • 76