1

I need to design a DB for storing key-value data. List of Key names isn't fixed and should expand at later point. I don't want to create a table with a column for each key, because I'll need to add columns frequently and it might grow too big, and I understood that the Entity-Attribute-Value design isn't efficient. I thought of the following type of table design, and I'm asking if it makes any sense and does anyone can come up with any drawbacks related to efficiency, table size or any other issue -

Each column in the table is of a different data type; irrelevant columns for a key are NULL:

ID |DataType |KeyName |ValInt |ValSmallInt|ValBool |ValStr  |ValText  |ValDate
-------------------------------------------------------------------------------
1  |1 (int)  |Width   |100    |NULL       |NULL    | NULL   | NULL    | NULL
2  |1 (int)  |Height  |200    |NULL       |NULL    | NULL   | NULL    | NULL
3  |2 (bool) |IsActive|NULL   |NULL       |false   | NULL   | NULL    | NULL
4  |3 (text) |URL     |NULL   |NULL       |NULL    | NULL   | w.com   | NULL
5  |1 (int)  |Size    |4      |NULL       |NULL    | NULL   | NULL    | NULL
6  |4 (date) |Created |NULL   |NULL       |NULL    | NULL   | NULL    | 2/2/2012

Number of columns will be as the number of all datatypes (about 30?).

The DataType column is a flag of type int which is used for the caller - it tells him what column to take the value from.

Each row is actually a property of some object in other table. A set of rows makes a set of properties for that object and usually a SELECT will get the whole set.

2

You're correct that representing different keys as columns in a table is a bad idea. The right thing to do is to represent them as different rows.

That leaves the problem of storing values of different types uniformly. The alternatives are

(a) storing them in different tables, at the cost of having to perform different queries for looking up values of different types, and having to use JOIN for retrieving all values. Depending on what your usage pattern will be, this may be a good idea. For instance, if you normally look up individual values rather than the entire set, and if the caller knows what type every key represents, then this is the superior solution.

(b) storing them all in the same table, at the cost of having to use the least common denominator for the type of the value column. This means having to cast or transform the value every time you retrieve it, and probably also storing a type flag in each record. This would be a horrible idea in an object-oriented programming language, because they have better options for polymorphic collections, but it can be the best solution for a database schema if you want efficient retrieval, or the caller doesn't necessarily know the type of each key.

Note that your solution would require more columns for the same data, and also require the caller to know which type to retrieve (or to check all the alternative fields to find out which one is not NULL). Therefore I would go with (a) or (b) instead, depending on what your typical usage pattern will be.

  • OK, Thanks. Actually I forgot to add a column for the caller :) which says what is the datatype for the value and the caller will always know where to look. And there will be a column for each and every datatype. Each row is related to some object in another table which serve as properties for the object and usually I will have to select a set of all properties altogether. Would you still recommend me to go with one of your methods? – Max Jul 5 '13 at 9:13
  • 1
    If you have an explicit type field and multiple columns in the record, then the caller will always have to execute a multi-way switch to decide which field to read. So you might just as well put type-casting or parsing code into those branches as well and save the overhead of the N-1 unused columns - unless parsing is very expensive or storage space very cheap. – Kilian Foth Jul 5 '13 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.