How to handle Settings for Accounts.

I’ve run into this problem a few times, but I’m putting out a call to see if anybody has some great insight into a good solution. To date, I’ve come up with three different approaches to handling user settings, but none of which I’m in love with. Other ideas, other comments, other ways?

Working assumptions

  • It’s handy to access specific elements outside of the database (e.g. who has XYZ preference set)
  • Databases appear to be better at storing small data than large (varchar < 255 vs. BLOB of 64k)
  • There is no right answer

Small records only

First encountered this at Excite with Excite Mail, since we were using OpenWave as the backend we could store 255 bytes in one of their database tables, and it became a preferences store. Though of course it was a bit more restrive than the below SQL, it got the job done. With a bit of class wrapping ontop, it really works.

Pro:

  • Only one record type

Con:

  • End up with a chained dkey (signature_0, signature_1, etc.)
CREATE TABLE IF NOT EXISTS setting (
    account_id      int unsigned NOT NULL,

    dkey            varchar(20) NOT NULL,
    data            tinyblob NOT NULL,
    created_on      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY     (account_id, dkey),
    FOREIGN KEY     (account_id) REFERENCES account(account_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Small and Big records

Ok, so when you don’t want to deal with chaining of records you can create a BIG and SMALL store of data.

Pro:

  • No chaining of records
  • Easily delete keys (just delete from both and ignore errors)

Con:

  • Lots of extra model level helpers to determine where data should go
CREATE TABLE IF NOT EXISTS user_data (
    user_id         varchar(16) NOT NULL,

    dkey            varchar(20) NOT NULL,
    dtype           int DEFAULT 0,
    data            tinyblob,

    created_on      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY     (user_id, dtype, dkey),
    INDEX           (user_id, dtype),
    FOREIGN KEY     (user_id) REFERENCES user(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_data_big (
    user_id         varchar(16) NOT NULL,

    dkey            varchar(20) NOT NULL,
    dtype           int DEFAULT 0,
    data            blob,

    created_on      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY     (user_id, dtype, dkey),
    INDEX           (user_id, dtype),
    FOREIGN KEY     (user_id) REFERENCES user(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Blob of doom

What about the idea that you don’t want your SQL store to have a clue, so save settings in one big blob (json, xml encoded, etc.)

Pro:

  • All the parsing is at code level, record management is easy (1 per user)

Con:

  • it’s a blob, you can’t really access it easily outside of your program
CREATE TABLE IF NOT EXISTS user_blob (
    user_id         varchar(16) NOT NULL,

    data            blob,

    created_on      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX           (user_id),
    FOREIGN KEY     (user_id) REFERENCES user(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Any other ideas? Cool suggestions, the magic solution I’ve forgotten?