we're trying to use Zimbra in production environment but I'm affraid of some misconceptions (IMHO) on the design of the database. May someone please help me understanding the reasons why you did it that way.

  • 1st: Why there's a database for each user ?

    The biggest problem is that Zimbra recommends EXT3 as a filesystem but EXT3 have a limitation of 32,000 hardlinks inside each hardlink and it's required to recompile the EXT3 module to change this number.

    Other problem is that you cannot select "all users that" without doing the query on *all* databases, this way you cannot build reports based on users' use of the tool, which is very important to ISPs.

    Despite all of that, it's ugly.

    I can think on some reasons, but none are good enough to stand against it's problems.
    - Scale: you can store N users on each node without changing the code for it.
    - Better solution: a table with usernames would do the same with a very little and coherent change in code. Each node could have only it's own set of users on the same table.

    - Removal: a drop database would remove the user and all it's data at once.
    - Better solution: doing a cascade on delete is not that hard even using MYISAM tables.

  • 2nd: Why MAILITEM table have a metadata field ?

    It's very hard to do searches (use of LIKE '%foo%' is not at all recomended), it's hard to understand what some row contains unless you remember what code maps to what "type" and it's not convenient to do JOINs.

    Also, you obligate the table to have some extra fields, like "filder_id" which not all rows will fill, thus creating a sparse table, which spends space.

    The concept of normalization is not an argument because, if everybody would normalize like that, there's no need of "relational" tables. A relational database is intended to have tables, normaly one for each item, relation and type.

    I just can't think why on earth someone would do that, sorry... It's worse for performance, space and it's *very* ugly.

Can anyone point me a better argument for that design ?