Design for relation database with object structure

Question

I am designing database for my app. I am using PostgreSQL. It has to be generic app and real world structure looks like this:

Category (e.g. vegetation) ---> Phenomenon (e.g. tree) ---> Parameters (e.g. type - coniferous vs. deciduous, height in meters - 10 and so on)

The database can store a lot of categories, phenomenons, parameters and their values. One category can have N phenomenons, one phenomenon can have N parameters.

So I created these tables:

Category
--------
id
name


Phenomenon
----------
id
name
category FK (to Category)


Parameter
---------
phenomenon FK (to Phenomenon)
name
value <-- here is a problem

In value column can be value from dictionary, varchar value, numeric value or boolean value. How can I design Parameter table? Should I make more columns for different types of value (varchar - can be dictionary value without integrity check, numeric, boolean). Or is there any design considering this problem? I don't want use JSON or XML.

I am really appreciate for any help.

reaanb · Accepted Answer · 2017-10-08 21:50:05Z

2

Your Parameter table is an EAV (entity-attribute-value) table and is not supported by the relational model. The RM is a first-order logical model which requires that every attribute have a single domain.

In your model, the domain of the value attribute depends on the value of the name attribute. So, it's not relational but it is implementable in SQL databases, though it's a pattern I avoid when possible since it makes integrity constraints difficult to impossible and complicates queries.

In first-order models, each distinct parameter would be in a separate relation, e.g.:

PhenomenonHeight
----------------
phenomenon FK (to Phenomenon)
height

PhenomenonType
----------------
phenomenon FK (to Phenomenon)
type

and so on.

edited Oct 8, 2017 at 21:50

answered Oct 8, 2017 at 21:37

reaanb

10.1k2 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bulva Over a year ago

Thx for the answer. But I am not sure about the example. It is separate table for every parameter? What if I will need to add new phenomenon and new parameters? I found some solutions on EAV (I wasn't able to name the problem right). In this presentation on slide 16, they have model with different column. Is this the same thing as you provide?

Bulva Over a year ago

Also found this: inviqa.com/blog/eav-data-model Looks very similar

reaanb Over a year ago

In a relational model, you can add new phenomena and new categories without changing the schema, but you would have to add a new table for each new parameter. Such are the limitations of a first-order model.

Collectives™ on Stack Overflow

Design for relation database with object structure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related