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:
CLOBfield - One will be null, the other will contain data, depending on the size of the value
- Keep a
VARCHARcolumn in the main table but store
CLOBs in a separate table - Then I can do one of:
OUTER JOINthe tables
- Use two separate queries to bring back data from each table
- Keep all of the metadata in the main table, add a
typefield and store all text data in its own tables Again, this depends on an
UNIONor 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).