Storing many small - and a few large - strings in an RDBMS Let's say I have a table, and (among other things) it needs to store a large number of strings. Most will be small (20-100 characters), but a few will be quite large. Reads will be frequent, writes will be rare.

What is a good general design for this type of table, using a traditional relational database?

Here are some of the things I have been thinking about:


A VARCHAR (let's say VARCHAR(200) and a CLOB field - One will be null, the other will contain data, depending on the size of the value
Keep a VARCHAR column in the main table but store CLOBs in a separate table - Then I can do one of:

OUTER JOIN the tables
UNION the tables
Use two separate queries to bring back data from each table

Keep all of the metadata in the main table, add a type field and store all text data in its own tables Again, this depends on an OUTER JOIN, UNION or multiple queries


The queries to return data from the table are fairly complicated, and I'm concerned that any solution involving multiple queries (or UNIONs) will be expensive to execute - but more importantly - complicated to maintain (i.e. two versions of almost identical queries).

Thoughts?
You failed to mention your DBMS but for Oracle and PostgreSQL you should simply put everything into a single CLOB column. 

The small values will automatically be stored "inline" and the large ones will be store out of line anyway. So the DBMS applies the logic of "switching" that you imply with your two column approach anyway. 

I would be surprised if other DBMS aren't doing the same thing.