[dev] Horde_Db best practices question

[Sebastian Birnbach]

I have an application that manages dossiers to which users can add
comments, much like user comments to a Whups ticket. I expect the number of
dossiers to grow into the 10.000s over the years and every dossier may
comprise up to a hundred comments.

The backend db is a mySQL.

One way to manage the comments would be to add each one into a big table
and include a reference to the dossier object. Lookup speed would degrade
with the number of comments in the table, though. Approaching the design
limit, selecting comments out of a million may be quite slow.

Another way would be to create a dedicated comments table for each record.
Selecting comments for a dossier would probably be much faster but all the
tables would also clutter table space. Plus I don't see a pre-fabricated
way to do this in the Horde framework and there is no example in the
existing applications either.

What is your recommended way of approaching this?

Thanks for your advice