[thelist] redesigning a huge database

[Mattias Thorslund]

Rick den Haan wrote:
> On 5/10/06, John Hicks <johnlist at gulfbridge.net> wrote:
>   
>> It looks like your requirements are:
>> --to add internationalization
>> --to optimize or tune the database and its queries
>> --to establish a system for rotating out old data
>>
>> .....check out 'analyze table'. This is a task that can
>> be delegated since it requires only DB expertise. (In many environments
>> a table of 700,000 rows would not be considered large!)
>>
>> One thing that you didn't make clear was whether a decision has already
>> been made on the scope of the project (redesigning and replacing v.
>> tweaking, revamping, optimizing etc.).
>>     
>
>
> John,
>
> Spot on on the requirements.
>
> I know that 700,000 rows can be considered moderate in some environments,
> but we don't usually build applications that required such large databases.
> So for us, this is huge.
>
> The decision has already been made that the app will be completely rebuilt
> from scratch. There's bound to be some things in the old app we can re-use,
> but we're not counting too heavily on it.
>
> Rick.
>   

One detail I might add is that the gettext utility is a handy way to add
internalization of text messages. With gettext, all translatable
messages stay in the source code but can be extracted into library files
that can be sent to translators.  There are translation editing
applications (such as poEdit) that makes this easy for the translator.

The gettext approach doesn't work well for messages stored in the
database itself, however.

Example:

<?php
    echo gettext("This will be printed in the user's preferred language,
if a translation is available. Otherwise, the original string is
returned as-is.");
?>


About the database tables: Indexes and query optimization does wonders
in improving response time, even on very large tables. Not to say that
you shouldn't also truncate your tables to remove old data.

HTH,

Mattias