[dev] Horde_Db best practices question

[Michael Menge]

Quoting Sebastian Birnbach <birnbacs at gmail.com>:

> followup: I would like to use the RDO driver for object access across
> tables.
> Any advice?
>
> 2017-11-13 9:41 GMT+01:00 Sebastian Birnbach <birnbacs at gmail.com>:
>
>> 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.
>>

If you have an index on the reference column the lookup speed should
not degrade much.


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

I would suspect that you move the lookup cost from looking in the
table to looking for the table in the database. Databases are designed
to handle referenced data in tables. But i don't know how good they are
designed to handle 10.000s of tables. In addition you need to keep track
of the tables.


>> What is your recommended way of approaching this?
>>

One "big" table for the comments and use the index.
http://use-the-index-luke.com/


--------------------------------------------------------------------------------
M.Menge                                Tel.: (49) 7071/29-70316
Universität Tübingen                   Fax.: (49) 7071/29-5912
Zentrum für Datenverarbeitung          mail:  
michael.menge at zdv.uni-tuebingen.de
Wächterstraße 76
72074 Tübingen