CODEWITHSUNDEEP
mysqldatabasedatabase-designdata-modeling
sp00m
sp00m

Reputation: 48807

How to manage key-value pairs storage when the value can be of several different types?

Let's say I've got n properties (pairs key-value) that are either dates, numbers or strings. Which data model is the most efficient to store them?

Here is a solution I thought about:

Data model

Each row of the data table must be able to be linked to n properties of several types.

The advantage of this solution is that if I want to add a new type, I just have to add two new tables (label_xy and value_xy) instead of modifying the structure of an existing table. But is this solution really the most convenient one?

How would you have done this?

Thanks :)

Upvotes: 4

Views: 1859

Answers (2)

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

I've used all of the scenarios:

  1. the one you described (each data type in own table)
  2. the one from Anthony's answer - with value only string casted to ither datatypes on demand
  3. one table with several columns with distinct datatypes, of them only one is used in each record (rest of them are sparse columns)

From those solutions: 1. was joining hell, 2. is good if your data is mainly strings, 3. although a bit of space-over-consuming is by far the best and most efficient.

As for 3. I must explain that the table looks like this:

EntityID int,
TypeID int,
LabelID int,
ValueInt int,
ValueDecimal decimal(12,4),
ValueString nvarchar(max),
ValueDate datetime

(Although LabelID defined TypeID I used both for join-less-usage purposes.)

Upvotes: 4

Anthony Palmer
Anthony Palmer

Reputation: 972

This really depends on the nature of the data. If you have millions of values and need strong typing then this might be one approach. However, as you mention, every new data type requires new tables.

If in reality you will only have a couple of thousand name value pairs, and the RANGE of data is fairly regular, i.e. numbers are ints, dates are 10 chars and strings are less than say 200 chars you could define all this in one table viz:

id int,
name varchar(50),
type int,
value varchar(200)
index(name)

The id is not required, but is good design.
The naming convention is up to you, can apply a unique constraint if necessary.
The type could be 1=int, 2=date, 3=string (others can easily be added later)
The value can be defined as per your max string.

This solution would favour simplicity over performance, but this will be decided by the requirements, the string datatype will be your wild card, it might make more sense to define it as "text".

Upvotes: 2

Related Questions