GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Blobs for property bags

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Blobs for property bags

    In several places we want the ability to store a 'property bag', i.e. a set of key:value pairs for some object (like a file or user). We don't query or sort on their values.

    One option is to do something like:
    CREATE TABLE file_properties (
    nFileId BIGINT NOT NULL,
    strKey VARCHAR(64) ASCII NOT NULL,
    strValue VARCHAR(512),
    PRIMARY KEY (nFileId, strKey)
    ) ENGINE=InnoDB CHARACTER SET utf8;

    The other:
    CREATE TABLE file_properties (
    nFileId BIGINT NOT NULL,
    blobProps BLOB,
    PRIMARY KEY (nFileId)
    ) ENGINE=InnoDB;

    Files typically have 6 or 7 props and are typically write once, ready many. We also have a cleanup process that deletes these in batch jobs. In the BLOB approach, we'd probably store things as a pickled (and probably compressed) python dictionary.

    What do people think? Is there an obvious winner here?

    thanks!!
Working...
X