Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to create a table (or several) to receive user inputs (UGC). This content could vary in size from a single character up to a few hundred words. The input will be coded in utf8_unicode_ci and could be in Latin or multi-byte characters.

The input will have to be search-able.

How should I structure my DB to allow for a good compromise between flexibility and performance? I could for example...

  1. Set up a high limit on the size of the string and take the performance & usability hits.
  2. Create several tables for various size ranges, and identify each item by a combination of table name and ID (so I'd need a central table with unique ID, table name, table-specific ID).
  3. Break down each input into its various components (i.e., words) and store the words separately, along with a relation that maps each statement to a series of word IDs...

I'm sure there are other options.

share|improve this question

closed as off topic by Ladadadada, Massimo, Ward, MDMarra, voretaq7 Aug 3 '12 at 5:44

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This is really a a database design question and would do better on our DBA site - Let me know if you would like it migrated. –  voretaq7 Aug 3 '12 at 5:44

1 Answer 1

up vote 0 down vote accepted

I guess it may depend slightly on your database engine, but more than likely it has a field for "Text" (as opposed to "Varchar" or "Char") that is suited for holding varying sizes of text data. Essentially this your option #1.

If it's going to be in MySQL then you have a Text or Blob field available: http://dev.mysql.com/doc/refman/5.6/en/blob.html

share|improve this answer

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