Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Which DBMS were you thinking of? There are probably different best-practices for each one. Also, how large is "large"? –  Jon Seigel Jul 13 '12 at 15:22
    
@JonSeigel: That's true. I'm looking for a general solution. I happen to be using Microsoft SQL Server for a project today, but this class of problem seems to arise quite often. –  Adam Batkin Jul 13 '12 at 18:33
add comment

1 Answer 1

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.

share|improve this answer
    
++ to what a_horse_with_no_name says. If this were DB2, then you would think even lower level such as tablespaces, etc. for LOB's vs other data. Also, it depends on the variable-ness of the lenghts of the strings. If they are pretty set, I'd go with char. If variable length, go with varchar. In DB2, you want to stick with varchar unless the string is greater than 32K, then you can go to CLOB. But if you are usually less than 32K, you'd take a performance hit to go with CLOB. Again...as is said before, it depends on the DBMS. –  Chris Aldrich Jul 13 '12 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.