如何在基于标签的组织中定义结构？

问题描述

我有一些实体，他们有一个系列的属性。一些属性影响实体可以具有的其他属性，许多属性被组织成组，并且偶尔实体需要从某些组中获得某些数量的属性，或者可能是某些组的某些属性范围。

有没有办法使用数据库建模这些类型的标签到标签的关系，例如要求，分组，排除等，或者这只能用编程的业务规则？理想情况下，我希望将可能的标签及其关系轻松配置，因此非常灵活。

我认为的一种方法是将标签和可能的标签关系，然后你得到一个标签应用关系类型的表，但这似乎是一个相当脆弱的方法。

所以，这是可能在一个更严格的时尚，如果是这样，我怎么会开始讨论呢？

解决方案

编辑您仅适用于其他属性中的值的变量属性的描述是非关系，非归一化设计。 RDBMS可能不是存储此类数据的最佳解决方案。可能RDF对于需要这种灵活性的数据来​​说将是一个很好的解决方案。

我以前的答案涉及RDBMS解决方案，如下所示：

$ b $有些人用设计，但这往往是非结构化的，你最终会遇到数据完整性问题。只有当您需要几乎无限数量的实体子类型时，才能使用此选项。

其他人使用，其中将所有子类型使用的所有属性列放入一个非常宽的表中，并将属性与子属性无关的行保留为NULL -类型。但是这有限制，因为表可能会变得太宽，并且您失去使任何属性强制的能力，因为它们都必须为空。

如果您有一个相对少量的实体子类型，我建议为每组必需的属性创建一个从属表。将从属表的主键定义为父表的外键，以便获得一对一的关系。

  CREATE TABLE车辆（
 vehicle_id INT PRIMARY KEY 
 ...所有车辆通用属性... 
）; 
 
 CREATE TABLE汽车（
 vehicle_id INT PRIMARY KEY，
 ...特定于汽车的属性... 
 FOREIGN KEY（vehicle_id）参考车辆（vehicle_id）
）; 
  

您还可以通过在父表的主键中对子类型进行编码来提供更多的数据完整性。这是为了确保汽车中的一行不能在车辆中引用摩托车。

  CREATE TABLE车辆（
 vehicle_id INT，
 vehicle_type VARCHAR（10），
 ...所有车辆共有的属性。
 PRIMARY KEY（vehicle_id，vehicle_type），
 FOREIGN KEY（vehicle_type）参考车辆类型（车型）
）; 
 
 CREATE TABLE Automobiles（
 vehicle_id INT，
 vehicle_type VARCHAR（10）CHECK（vehicle_type ='Automobile'），
 ...特定于汽车的属性.. 
 FOREIGN KEY（vehicle_id，vehicle_type）
参考车辆（vehicle_id，vehicle_type）
）; 
  

当然，您需要在每次定义新的子类型时创建一个新的依赖表，但是这种设计确实为您提供了更多的结构来强制数据完整性，NOT NULL属性等等。

在应用程序逻辑中需要强制执行的唯一部分是确保在 Automobiles 中创建一行车辆中的每一行车辆与 vehicle_type ='汽车'。

[former title: Is there a way to force a relationship structure on a tag-based organizational methodology?]

I have some entities, and they have a series of attributes. Some of the attributes affect what other attributes the entity can have, many of the attributes are organized into groups, and occasionally entities are requited to have certain numbers of attributes from certain groups, or possibly a range of attributes from certain groups.

Is there a way to model these sorts of tag-to-tag relationships, such as requirement, grouping, exclusion, etc. using a database, or is this only possible with programmed "business rules"? Ideally, I would like the possible tags and their relationships to be easily configurable, and hence highly flexible.

One of the ways I have considered is to have the tags and possible relationships, and then you get a tag-tag-applied relationship sort of table, but this seems like a rather brittle approach.

So, is this possible in a more rigorous fashion, and if so, how would I even begin to go about it?

解决方案

edit: Your description of variable attributes that apply only depending on the values in other attributes is a non-relational, non-normalized design. RDBMS may not be the best solution for storing this kind of data. Probably RDF would be a good solution for data that requires this level of flexibility.

My earlier answer, pertaining to RDBMS solutions, is below:

---

Some people model flexible attributes with the Entity-Attribute-Value design, but this is often too unstructured and you end up fighting with data integrity problems. Use this only if you need a virtually limitless number of entity sub-types.

Other people use Single Table Inheritance, where you put all attribute columns used by all sub-types into one very wide table, and leave them NULL on rows where the attribute is irrelevant to the sub-type. But this has limits because the table can grow too wide, and you lose the ability to make any attributes mandatory, because they must all be nullable.

If you have a relatively small number of entity sub-types, I would recommend creating a dependent table for each group of required attributes. Define the dependent table's primary key as a foreign key to the parent table, so you get a one-to-one relationship.

CREATE TABLE Vehicles (
  vehicle_id INT PRIMARY KEY
  ...attributes common to all vehicles...
);

CREATE TABLE Automobiles (
  vehicle_id INT PRIMARY KEY,
  ...attributes specific to autos...
  FOREIGN KEY (vehicle_id) REFERENCES Vehicles(vehicle_id)
);

You can also provide a little more data integrity by encoding the subtype in the primary key of the parent table. That's to make sure a row in Automobiles cannot reference a motorcycle in Vehicles.

CREATE TABLE Vehicles (
  vehicle_id INT,
  vehicle_type VARCHAR(10),
  ...attributes common to all vehicles...
  PRIMARY KEY (vehicle_id, vehicle_type),
  FOREIGN KEY (vehicle_type) REFERENCES VehicleTypes (vehicle_type)
);

CREATE TABLE Automobiles (
  vehicle_id INT,
  vehicle_type VARCHAR(10) CHECK (vehicle_type = 'Automobile'),
  ...attributes specific to autos...
  FOREIGN KEY (vehicle_id, vehicle_type)
    REFERENCES Vehicles(vehicle_id, vehicle_type)
);

Of course, you need to create a new dependent table each time you define a new sub-type, but this design does give you a lot more structure to enforce data integrity, NOT NULL attributes, and so on.

The only part you need to enforce in application logic is that to make sure to create a row in Automobiles for each row in Vehicles with vehicle_type = 'Automobile'.

这篇关于如何在基于标签的组织中定义结构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！