Database design with blobs - keep in separate tables?

Question

I have a situation where I need to store binary data in an Oracle database as blob columns. There are three different tables in my database wherein I need to store a blob data for each record. Not every record will necessarily have the blob data all the time. It is dependent on time and user.

• Table One will store *.doc files for almost each record.
• Table Two will store the *.xml optionally.
• Table Three will store images (frequency unknown)

Is this a good approach of maintaining a separate table to store the various blob data pointing it to the respective table PK's? (Yes, there will be no FKs, I'm assuming the program will maintain it). It will be some thing like below,

BLOB|PK_ID|TABLE_NAME

Alternatively, is it a good idea to keep the blob columns in separate tables?

As far as my application runtime is concerned,

Table 2 will be read very frequently. Though the blob column will not be required.
Table 2's records will be deleted frequently.

The other blob data in respective tables will not be accessed frequently. All of the blob content will be read on an as-needed basis.

I'm thinking first approach will work better for me. Any concerns on this design in terms of performance or maintainability?

Answer 1

If there is a one-to-one relationship between a given record and a LOB then the best idea is to declare the LOB column in the record. Oracle allows us to declare a separate tablespace for LOBs, so there is not much impact on storage.

create table t23
  ( id number not null
    , col1 number not null
    , col2 date not null
    , col3 varchar2(255)
    , a_doc clob  
    , x_doc xmltype
    , constraint t23_pk primary key (id)
  )
tablespace app_date 
lob (a_doc) store as basicfile a_lob (tablespace lob_data)
lob (x_doc) store as securefile x_lob (tablesapce xml_data)
/ 

(SECUREFILE is an Enterprise Edition feature introduced in 11g. Find out more).

The main thing about this approach is that you will have to explicitly specify the columns you want to select if you don't want to include the LOB columns. This shouldn't be a hardship as it is best practice: select * from .... is a bug waiting to happen.

"The table three will have to store images"

If you have a one-to-many relationship then you will need to have a separate table for Images.

There are a lot of subtleties when it comes to storing LOBs and tuning the affected queries. I recommend you read this Oracle white paper, from the OTN website.