[dev] Horde_Db best practices question

[Ralf Lang]

Am 13.11.2017 um 09:41 schrieb 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.

It should not -
use an int primary key for dossier id
use an int primary key for comment id
have an index on dossier_id in the comment table

If this isn't fast enough, go for the database's native options.
Mysql/MariaDB support partitioning of data. For example, partition by
hashes of dossier IDs. However, don't overdo.

Getting 100 out of 1.000.000 columns by an appropriate index is not much
for mysql, properly sized and configured.

> 
> 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.

You would need to use horde_db's schema modifying functions in live
code. Better don't. If you ever need a schema upgrade, it will become
quite complicated.

Regards

Ralf

-- 
Ralf Lang
Linux Consultant / Developer
Tel.: +49-170-6381563
Mail: lang at b1-systems.de
B1 Systems GmbH
Osterfeldstraße 7 / 85088 Vohburg / http://www.b1-systems.de
GF: Ralph Dehner / Unternehmenssitz: Vohburg / AG: Ingolstadt,HRB 3537

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: OpenPGP digital signature
URL: <https://lists.horde.org/archives/dev/attachments/20171115/8a3a2346/attachment.bin>