在数据库中为用户定义的层次关系建模的最佳方法是什么？

Essentially, I want the user to be able to define a hierarchical model, but then I need to allow the user to store data within their defined model. Does this make sense? So the users will be able to create new "unit types" to be organised in a hierarchical way and decide how units of these types are allowed to be organised. A simple example: in my hypothetical interface a user creates three unit types, trunk, branch and leaf. The user then defines the relationships between them. A leaf can exist at any point in the hierarchy, a branch must have a trunk as a parent. The user can then creates instances of these unit types (as units) and can organise them according the rules defined in their model... is there a good way of doing this in the database?

本质上,我希望用户能够定义分层模型,但我需要允许用户在其定义的模型中存储数据。这有意义吗?因此,用户将能够以分层方式创建新的“单元类型”,并决定如何组织这些类型的单元。一个简单的例子:在我的假设界面中,用户创建三种单元类型,trunk,branch和leaf。然后,用户定义它们之间的关系。叶子可以存在于层次结构中的任何点,分支必须具有主干作为父级。然后,用户可以创建这些单元类型的实例(作为单元),并可以根据其模型中定义的规则对其进行组织...有一种在数据库中执行此操作的好方法吗?

3 个解决方案

#1

This is an extremely broad question, but this may point you in the right direction. Note that you're only going to be able to store the relationship rules in the database. Enforcing them will be up to your client code. Try this on for size..

这是一个非常广泛的问题,但这可能会指向正确的方向。请注意,您只能将关系规则存储在数据库中。执行它们将取决于您的客户端代码。尝试这个尺寸..

unit:
    unit id,
    name,

unit relationship:
    unit id,
    foreign unit id

You can then use your unit relationship table in the following way..

然后,您可以按以下方式使用单位关系表。

unit id relates to the unit it's describing. foreign unit id should be nullable.

unit id与它描述的单位有关。外国单位id应该可以为空。

A unit with no relationship records can only exist at the root of the heirarchy. A unit with a null foreign unit id can have any other unit as its parent. Otherwise, a unit must have another unit as its parent, and it's type must be one of those defined in its relationship records.

没有关系记录的单位只能存在于层次结构的根部。具有空外部单元ID的单元可以具有任何其他单元作为其父单元。否则,一个单元必须有另一个单元作为其父单元,并且它的类型必须是其关系记录中定义的单元之一。

As for storing the instances themselves, that should be straightforward..

至于存储实例本身,这应该是直截了当的..

instance:
    instance id,
    unit id,
    parent instance_id

I'm sure there would be other fields you'd need (name, for instance), but I assume you get the drift.

我敢肯定会有你需要的其他领域(例如名字),但我认为你得到漂移。

#2

You need to implement three concepts:

您需要实现三个概念:

the "unit types" and their allowed associations

“单位类型”及其允许的关联

the hierarchy
the actual units

实际单位

These concepts can coexist more or less independently in the model, but work together.

这些概念可以在模型中或多或少独立共存,但可以协同工作。

create table unittype
(
    id int;
    name varchar(20);
)

create table unitrelationship
(
    id int;
    parent_id int;
)

You could model the hierarchy as self-referencing table:

您可以将层次结构建模为自引用表:

create table hierarchy
(
    id int;
    parent_id int;
    unit_type_id int;
    unit_id int;
)

You can then have your unit instances in one or more tables and do with them what you described.

然后,您可以将您的单元实例放在一个或多个表中,并按照您的描述进行操作。

create table unit
{
    id int;
    ....
}

The good news is that you are constraining only the allowed parent types, which can be easily enforced in a user interface, for instance by picking the parent from a list of all existing units of the allowed type.

好消息是,您只限制允许的父类型,这可以在用户界面中轻松实施,例如从允许类型的所有现有单元的列表中选择父级。

#3

I'm working on a similar issue although I need to support multiple hierarchies (one set of children, multiple hierarchical views). I've found Joe Celko's "Trees and Hierarchies in SQL for Smarties" (ISBN: 1558609202) useful. I'm still working on the problem but it comes up so often when discussing this topic that it seemed appropriate to mention.

虽然我需要支持多个层次结构(一组子层,多个层次视图),但我正在处理类似的问题。我发现Joe Celko的“树和层次结构在SQL for Smarties”(ISBN:1558609202)很有用。我仍然在研究这个问题,但是在讨论这个话题时经常会出现这个问题。

#1