tall_man's warning about the risk of spelling errors in attribute names is worth heeding. If your potential range of attributes is in the tens (or dozens), you could have a single attribute table keyed by blob_id, with a separate column (suitably named) for each possible attribute.
If you need to add more attributes as time goes by -- and if you don't mind the slight extra effort needed each time you add one -- you can alter your attribute table at any time by just adding columns.
This way, you have just two tables, and doing a join-type query is really simple: "select blob_table.data from blob_table, blob_attrib where blob_table.id = blob_attrib.id and blob_attrib.color = 'blue' [and blob_attrib.flavor = 'grape' and ...]" ...whatever.
| [reply] |
Yes, I would say that it's closer to your first approach (attributes in a key/value table) than to your second (attributes in a blob you have to decode yourself).
Another couple of reasons that completely separate tables might be better: there is no redundant attribute-key field to store, and you avoid attribute-key spelling errors (like "color" vs. "colour"). If the attributes are entered through a user interface, how are you going to check for valid attribute names? | [reply] |