Complex Database table design

Question

I have a scenario where I am saving different parameters with values in database. Parameters can be of many types. i.e.

String, Integer, Multivalue

As it is one to many relation so I create two tables for it.

parameter
---------
id | name

parameter_value
---------------
id | parameter_id(F.K) | value

Now another parameter type added to requirements. i.e 'map'. So a parameter can be a map. Map can have other parameters. I am having problem in designing database tables according to this new requirement. Should I need to create new tables or change existing one? Using IBM DB2.

possible duplicate of [Database EAV Pros/Cons and Alternatives](http://stackoverflow.com/questions/2224234/database-eav-pros-cons-and-alternatives) — Pondlife, Mar 27 '12 at 13:39
You should probably mention which database platform you're using, as different systems may have different features that apply here. But essentially, you're asking how/if to implement an EAV model. — Pondlife, Mar 27 '12 at 13:40
I don't know EAV model and does not want to implement that. Just want to use simple table. Using DB2. — Muhammad Imran Tariq, Mar 27 '12 at 14:00
@imran: Why don't you use a separate table for every type and another table to associate every parameter name with a type name? — Niklas B., Mar 27 '12 at 14:06
@imran tariq: I'm using Oracle's *Berkeley DB Java Edition* to store key/value pairs where the values are maps made of unstructured "parameters". If your data isn't structured then SQL is not necessarily the best choice. — TacticalCoder, Mar 27 '12 at 14:13
@imran: Ahm, sorry, this is not how it works. I just suggested a design which could work, now it's your turn to evaluate that. — Niklas B., Mar 27 '12 at 15:46

Mike Sherrill 'Cat Recall' · Answer 1 · 2012-03-27T16:51:53.230

You said "Parameters can be of many types. i.e. String, Integer, Multivalue". But tables like this

parameter_value
---------------
id | parameter_id | value

can store only one type, probably some kind of varchar(n), where 'n' might be really big. So parameters that are supposed to be integers might end up being 'wibble'.

Also, "Multivalue" isn't historically a relational or SQL data type. (That's changing. But change is slow.) Some SQL platforms support some kinds of multiple values, but support is far from universal. PostgreSQL, to pick one I'm pretty familiar with, supports arrays, JSON (I think, in the latest version), and key/value pairs (hstore module). One of those might help you. I'm not sure.

Complex Database table design

1 Answers1