基于对象结构的关系数据库设计。

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

我正在为我的应用设计数据库。我正在使用PostgreSQL。它必须是通用的应用程序，现实世界的结构是这样的:

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

类别(如植被)--->现象(例如树)--->参数(例如:类型-针叶树与落叶，高度在米- 10等)

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.

数据库可以存储许多类别、现象、参数和它们的值。一个类别可以有N个现象，一个现象可以有N个参数。

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.

值列可以是字典、varchar值、数值或布尔值。如何设计参数表?是否应该为不同类型的值(varchar -可以是dictionary值，而不需要完整性检查、数字和布尔值)创建更多的列。或者有设计考虑这个问题吗?我不想使用JSON或XML。

I am really appreciate for any help.

非常感谢您的帮助。

1 个解决方案

#1

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.

您的参数表是一个EAV(实体-属性-值)表，不受关系模型的支持。RM是一个一阶逻辑模型，它要求每个属性都有一个域。

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.

在模型中，值属性的域取决于name属性的值。因此，它不是关系型的，但在SQL数据库中是可实现的，尽管我尽可能避免这种模式，因为它使完整性约束变得难以实现，并使查询变得复杂。

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.

等等。

#1