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...
- Set up a high limit on the size of the string and take the performance & usability hits.
- 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).
- 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.