Blobs for property bags

MySQL & MariaDB Other MySQL® Questions
1

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

1 Like