Posted on 6 Comments

Horde’s schema

Homework! Below is a fairly random extract from the database schema of Horde 3.0.7… what’s wrong with this:

CREATE TABLE horde_prefs (
    pref_uid        VARCHAR(200) NOT NULL,
    pref_scope      VARCHAR(16) NOT NULL DEFAULT '',
    pref_name       VARCHAR(32) NOT NULL,
    pref_value      LONGTEXT NULL,

    PRIMARY KEY (pref_uid, pref_scope, pref_name)
);

Please post your insights and conclusions as comments to this post…

Posted on 6 Comments

6 thoughts on “Horde’s schema

  1. A VARCHAR (200) for a unique id is strange. Also, the data type ‘LONGTEXT’ for the value of a preference is odd … I would expect some kind of controlled list of possible values.

  2. is it some kind of a joke? 🙂 and their DB still works? unbelievable! 🙂
    “what’s wrong with this” – everything! 😉

  3. The primary key needs to be about 248 chars long. They might be better off adding an auto_increment column as a primary key, then just adding a unique index over (pref_uid, pref_scope, pref_name).

    You haven’t mentioned what storage engine is being used, but with InnoDB in particular, short primary keys are important.

    – Morgan Tocker

  4. I dont see anything particularily wrong with this layout.
    See the following post on the topic of surrogate keys:
    http://pooteeweet.org/blog/299

    I doubt this table is particularily performance relevant anyways, so why add additional overhead and meaningless data for the administrator?

    I presume that they use a varchar for the user id, since a user id could for example be a string from some LDAP backend. Again why introduce a surrogate key? That being said it might make sense to introduce a surrogate key for the user id, but certainly not as a replacement for the entire current primary key. I have a hard time seeing them doing a join on such a surrogate key “pref_id” without also joining on at least some of the current primary key columns. Oh and the fact that innodb actually puts a clustered key on primary key fields means that the current layout minimizes the number of pages to read when working with the data of a specific user. A fairly likely usage scenario: delete/read all prefs for a user.

    Personally I have gone with a much simpler layout for my framework like no “scope”. We also dont have a name and value column, but we simply put a serialized array for every user that stores preferences in a single row since we have no intention of querying that data anyways. Bring on your DBA hate mails, I ain’t scared 🙂

    So I guess the point is:
    If they do not have performance issues, then all is well. Otherwise moving to fixed length columns could also help. Maybe they might explore doing surrogate keys for the user id for this purpose. Introducing a surrogate key for scope and name seems madness to me and moving to CHAR could atleast get the key to be fixed length.

  5. Surrogate keys have uses. That’s not really a good reason for choosing a huge one that’s hard to work with when a small one which is easy to work with will do the job.

    You’re right that the InnoDB clustering by primary key works with this PK choice but an integer would work as well. This physical layout is one huge advantage InnoDB has over MyISAM (even when transactions aren’t needed) and PostgreSQL. It’s one of the key reasons why only five database servers are needed by Wikipedia and why switching to PostgreSQL would be a really lousy idea for that system – it’s architecture is inherently far less physically efficient.

  6. […] Horde’s schema II Filed under: Uncategorized | — arjen @ 12:07 pm […]

Comments are closed.