数据库设计：具有不同属性的对象 [英] Database design: objects with different attributes
问题描述
我正在设计一个产品数据库，其中产品可以具有非常不同的属性，具体取决于其类型，但属性对于每种类型都是固定的，类型根本不可管理。例如：
I'm designing a product database where products can have very different attributes depending on their type, but attributes are fixed for each type and types are not manageable at all. E.g.:
杂志： title，issue_number，pages，copies，close_date，release_date
web_site： / strong>名称，带宽，命中，date_from，date_to
magazine: title, issue_number, pages, copies, close_date, release_date
web_site: name, bandwidth, hits, date_from, date_to
我想使用InnoDB并且强制数据库完整性，尽可能多的引擎允许。有什么推荐的方法来处理这个？
I want to use InnoDB and enforce database integrity as much as the engine allows. What's the recommended way to handle this?
我讨厌那些表有100列的设计，大部分的值都是NULL，所以我想到了这样的事情：
I hate those designs where tables have 100 columns and most of the values are NULL so I thought about something like this:
product_type
============
product_type_id INT
product_type_name VARCHAR
product
=======
product_id INT
product_name VARCHAR
product_type_id INT -> Foreign key to product_type.product_type_id
valid_since DATETIME
valid_to DATETIME
magazine
========
magazine_id INT
title VARCHAR
product_id INT -> Foreign key to product.product_id
issue_number INT
pages INT
copies INT
close_date DATETIME
release_date DATETIME
web_site
========
web_site_id INT
name VARCHAR
product_id INT -> Foreign key to product.product_id
bandwidth INT
hits INT
date_from DATETIME
date_to DATETIME
这可以处理级联的产品删除，但是...我不完全相信...
This can handle cascaded product deletion but... Well, I'm not fully convinced...
推荐答案
这是经典OO设计的关系表阻抗不匹配。您所描述的表格设计称为每个子类表。这三个最常见的设计与您的应用程序中实际看起来的对象相比都是妥协的：
This is a classic OO design to relational tables impedance mismatch. The table design you've described is known as 'table per subclass'. The three most common designs are all compromises compared to what your objects actually look like in your app:
- 每个具体类的表
- 每个层次结构的表
- 每个子类的表
设计你不喜欢 - 表中有100列，大多数值为NULL - 是一个用于存储整个专业化层次结构的表。对于各种原因，这是最不灵活的，包括 - 如果您的应用需要新的子类，则需要添加列。您描述的设计适应更改更好，因为您可以添加扩展它，通过添加由product_type中的值描述的新子类表。
The design you don't like - "where tables have 100 columns and most of the values are NULL" - is 2. one Table to store the whole specialization hierarchy. This is the least flexible for all kinds of reasons, including - if your app requires a new sub-class, you need to add columns. The design you describe accommodates change much better because you can add extend it by adding a new sub-class table described by a value in product_type.
其余选项 - 1。每个具体类的表通常是不合需要的，因为实现每个专业化表中所有公共字段的重复。尽管优势在于，您不需要执行任何连接，子类表甚至可以在非常大的系统中的不同数据库实例上。
The remaining option - 1. Table per concrete class - is usually undesirable because of the duplication involved in implementing all the common fields in each specialization table. Although, the advantages are that you wont need to perform any joins and the sub-class tables can even be on different db instances in a very large system.
设计您描述是完全可行的。下面的变化是如果您使用ORM工具来执行您的CRUD操作，它的外观如何。请注意每个子类表中的ID是否是层次结构中父表的FK值。一个好的ORM将根据product.id和product.product_type_id中的鉴别器值的值自动管理正确的子类表CRUD。无论您是否计划使用ORM，请查看hibernate的加入子类文档，只查看他们所做的设计决策。
The design you described is perfectly viable. The variation below is how it might look if you were using an ORM tool to do your CRUD operations. Notice how the ID in each sub-class table IS the FK value to the parent table in the hierarchy. A good ORM will automatically manage the correct sub-class table CRUD based on the value of the discriminator values in product.id and product.product_type_id alone. Whether you are planning on using an ORM or not, look at hibernate's joined sub-class documentation, if only to see the design decisions they made.
product
=======
id INT
product_name VARCHAR
product_type_id INT -> Foreign key to product_type.product_type_id
valid_since DATETIME
valid_to DATETIME
magazine
========
id INT -> Foreign key to product.product_id
title VARCHAR
..
web_site
========
id INT -> Foreign key to product.product_id INT
name VARCHAR
..
这篇关于数据库设计：具有不同属性的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
