DB table with column for each datatype

Question

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.

Answer 1

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.