Blobs and clobs in design 1

[pgirl]

Hi,

I'm trying to design a content management database (logical design only), but I'm stuck.

The database must store multimedia, and the documents must be searchable, so I have a content table.

Since some of the data will be blob, and some clob - I obviously can't store it all in the same table for normalisation reasons, but if I have seperate tables for blob and clob data.. how can I link them back? I'm stuck with the same problem of one field being empty..

I think I'm missing something fundamental..

Any help greatly appreciated.

 

[ddrillich]

We have a table called CONTENT which has these exact two columns (and several others):
content_body as a clob and file_body as a blob.
Do you think that it’s a violation of the first normalization rule?

Thanks,
Dan

 

[pgirl]

Hi ddrillich,

I have since submitted the design of the table exactly as you have yours.

Since they both depend on the primary key, it doesn't violate the first rule of normalisation, but I was wrestling with concept of a column called "content" which could contain either a blob for a binary file or a clob for a character file, which of course is not possible to have one column represent more than one type of data or value.

It seemed to create a lot of null entries to have both columns - but in thinking about it, it does seem the logical way to go. A file could have both components to it, or a user may wish to upload the text and a logo etc, or break out embedded images.

I know that some people think you shouldn't insert blobs and clobs into databases at all. It's been said that file systems handle files much better than databases and that storing just the "path" in the database greatly improves performance - but storing clobs in databases means that you can perform data manipulation (especially selects), and provides physical independence (i.e. if you store the file path and then someone moves the file- you've lost it).

Thanks for your answer