sql - 可选结构sql

Question

DB 世界不是我的，所以这个问题也许是微不足道的

想象一下设计一个存储来自不同类型项目(如数据类型)的数据的数据库

我不知道我应该怎么做，但这就是我正在做的

id | quantity | price ... Kind | ID_k_foreing |

那么对于每种类型的项目将是一个具有每种类型属性的表

所以通过软件我可以比较种类，然后连接到适当的表

像这个伪代码

switch(kind)
{
case chess_game:
      the join is made with a table like this:
      id_k| material | length | weigth ..
case car_toy:
      the join is made with a table like this:
      id_k| color | velocity | remote_control ...
case doll:
      the join is made with a table like this:
      id_k| name | height | clothes ..

...

}

有一些标准方法可以解决结构中的这种“数据类型”问题
不添加棘手的软件功能？

谢谢

Answer 1

你可能想看看这个question .

比尔·卡尔文的 answer以这种方式分解它。

You have at least these five options for modeling the type hierarchy you describe:

Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row.

Class Table Inheritance: one table for Products, storing attributes common to all product types. Then one table per product type, storing attributes specific to that product type.

Concrete Table Inheritance: no table for common Products attributes. Instead, one table per product type, storing both common product attributes, and product-specific attributes.

Serialized LOB: One table for Products, storing attributes common to all product types. One extra column stores a BLOB of semi-structured data, in XML, YAML, JSON, or some other format. This BLOB allows you to store the attributes specific to each product type. You can use fancy Design Patterns to describe this, such as Facade and Memento. But regardless you have a blob of attributes that can't be easily queried within SQL; you have to fetch the whole blob back to the application and sort it out there.

Entity-Attribute-Value: One table for Products, and one table that pivots attributes to rows, instead of columns. EAV is not a valid design with respect to the relational paradigm, but many people use it anyway. This is the "Properties Pattern" mentioned by another answer. See other questions with the eav tag on StackOverflow for some of the pitfalls.

你必须弄清楚哪一个适合你。