Design a set of attributes

Question

Hello everyone! My boss gave me a bunch of requirements that I'm not pretty sure about how to design them in the DBMS. Basically the user has to describe an object defining its attributes in an old fashion way: "key"=>"value". The problem is that the user has "free will" on choosing both keys and values so he can wrote whatever he wants about this object. Now, this objects belongs, of course, to certain categories so, in my opinion, even with the free will mode on I don't think we should have more than 15/20 keys per category(=>object).

My boss told me to structure it in the following way (that's just the skeleton):

Table objects(id[primary key], name)

Table pairs(id[primary key],obj_id[foreign key to objects table], key, value)

But I'm not really convinced. In my opinion it should be something like this:

Table objects(id[primary key], name)

Table pairs(id[primary key],obj_id[foreign key to objects table], key_id[foreign key to keys table], value)

Table keys(id[primary key], key[unique])

And of course the column key will be sanitized and redundancy will be limited.

Am i right, am I overthinking it or am I just wrong?

Answer 1

Personally, I think you're both wrong. I would go with:

Objects 
-------
id (PRIMARY_KEY)
name

Keys
----
id (PRIMARY_KEY)
value

KeyValuePairs
-------------
id (PRIMARY_KEY)
keyId (FOREIGN_KEY(Keys))
value

Objects_KeyValuePairs
---------------------
objectId (PRIMARY_KEY, FOREIGN KEY (Objects))
kvpId (PRIMARY_KEY, FOREIGN KEY (KeyValuePairs))

Now you have a table to store the Key/Value pairs ("key" => "value"), a table to store the parent, and a table to store the many/many relationship that Key/Value pairs may have with Objects.