SQLite Forum

General question concerning database stucture and number of databases to use.
Login
Thank you. I didn't ask the question correctly nor provide sufficient information. I wasn't trying to ask about separating data on the same row into two tables but whether or not CLOBs and BLOBs of different sizes should be stored in the same column of the same table.

There are two categories of data that need stored for the application I'm asking about.  Users build small documents inside the application through adding UI elements and then input text or link media files (audio, video, PDFs) within those elements.

The position, sequence order, size, and everything else concerning each document element except the actual text or media file itself is stored within a map object in a different table. Each map object can have any number of text blocks and media files associated with it.  They are stored in another table and have the primary key of the map object as a column. That table has only three columns: the auto-generated rowid, the primary key of the associated map object, and the text or media data.  The only column in that table that will ever be updated/replaced is the text/media column.

My question is are two tables required for the text and media data, that is, one for CLOBs and one for BLOBs?  I ask for two reasons.

One is that due to the nature/structure/purpose of these small documents, at least 95% of the time each CLOB will never exceed more than a small paragraph of text and will always be much smaller than any of the BLOB media files. I think the text rows will almost always be less than the default page size and the BLOB rows will always exceed it.  The other is that I wasn't sure if it is safe practice to declare a column as a BLOB and frequently write text to it also, relying on the column affinity rules to accept text in the BLOB column.

There will be two general database tasks associated with this portion of the data. One is to retrieve a map object and all CLOBs and BLOBs associated with that map's primary key, and pass them to the application to contruct the requested document. The other is to either overwrite a CLOB or add/delete a BLOB record as a user builds/edits a document.

Thank you.