3

This question is an attempt to find a practical solution for this question.

I need a semi-schema less design for my SQL database. However, I can limit the flexibility to shoehorn it into the entire SQL paradigm. Moving to schema less databases might be an option in the future but right now, I' stuck with SQL.

I have a table in a SQL database (let's call it Foo). When an row is added to this, it needs to be able to store an arbitrary number of "meta" fields with this. An example would be the ability to attach arbitrary metadata like tags, collaborators etc. All the fields are optional but the problem is that they're of different types. Some might be numeric, some might be textual etc.

A simple design linking Foo to a table of OptionalValues with fields like name, value_type, value_string, value_int, value_date etc. seems direct although it descends into the whole EAV model which Alex mentions on that last answer and it looks quite wasteful. Also, I imagine queries out of this when it grows will be quite slow. I don't expect to search or sort by anything in this table though. All I need is that when I get a row out of Foo, these extra attributes should be obtainable as well.

Are there any best practices for implementing this kind of a setup in a SQL database or am I simply looking at the whole thing wrongly?

Community
  • 1
  • 1
Noufal Ibrahim
  • 71,383
  • 13
  • 135
  • 169
  • Are you targeting a particular DBMS or do you want to keep this generic? – Will A Aug 01 '10 at 11:25
  • I'm using `mySQL` right now so atleast for the moment, if there's a good solution that's `mySQL` dependent, it's fine. – Noufal Ibrahim Aug 01 '10 at 11:29
  • Is it the case that this metadata is merely a wad of data such that you will never need to filter for a specific metadata attribute nor need to place a specific metadata attribute in a specific place on a report nor do aggregate analysis on specific metadata attributes but instead would only be used for informative purposes to spit out the wad of metadata for a given value? – Thomas Aug 01 '10 at 16:37
  • Well, suppose it was a set of key/value pairs, the actual names of the keys might come from another table once during creation of this row. It's not a completely isolated thing like, for example, an image file the only tie up of which to the database is a text field indicating it's location on the files system. – Noufal Ibrahim Aug 01 '10 at 17:12
  • @Noufal Ibrahim - Specifically, I'm asking whether you can enforce that no developer will write a query akin to `Where [Attribute] = 'Foo'` in any report or output nor would they ever have code that does something with a specific attribute? The answer will determine whether an EAV might be a viable option. – Thomas Aug 01 '10 at 18:02
  • Yes. I can guarantee that they won't write such queries. If it does become a necessity later, we can move that specific attribute into Foo and make it "Standard" rather than "meta". – Noufal Ibrahim Aug 02 '10 at 04:16

1 Answers1

1

Add a string column "Metafields" to your table "Foo" and store your metadata there as an XML or JSON string.

Doc Brown
  • 19,739
  • 7
  • 52
  • 88