Posted by Markus Ernst on 01/13/06 17:03

BLob schrieb:
>>I use a table 'strings' with the fields:
>>- stringnumber
>>- language
>>- string
>
> I thought about this structure, but I don't know if it is better or worse
> than the one I proposed in my first post. 'string' has to be a 'text' field
> in the database in order to hold both data from what would otherwise be
> varchar and text. Isn't this a problem ? (I don't know).

According to the MySQL manual a "text" field requires as much disk space
as the size of the text it holds (plus 1 byte). That's why I think it is
not a problem to use the "text" data type also for very short strings; I
actually don't understand the reason why most developers use varchar
fields with specified string lengths. But maybe I am wrong here.

> Yes, it makes it easier for the 'translation' process. But on the other hand
> I guess it makes it more complicated to search within information (search
> engine), as you don't know what type of data you have without making links
> to other tables. That's why I chose the other approach, but maybe I am wrong
> and there is a pretty work around do do efficient searches with the
> structure you described. What do you think ?

I built a special search index table where I store all information for
every object that has to be searchable centrally. This is extra
programming work, but I see several advantages in it:

- As the contents of a page is centralized in one field you can use the
MySQL fulltext search and take advantage of its boolean and ranking
capabilities
- When storing the contents to the search index you can pre-process it,
for example remove punctuation and convert accented characters and
whatever, which can improve the performance and quality of the search
process
- If you need to add another type of objects - say "products" - you
don't have to edit your search process, but just add the appropriate
"store to search index" method to the "products" administration.

--
Markus

[Back to original message]