产品具有多个类别类型的数据库模式 [英] product with multiple category type database schema

问题描述

我想存储关于他们分类的歌曲文件的信息：

1.类型：例如：pop，rock，classical等。喜欢钢琴小提琴等等_7 $ b $ 3.每首歌都有许多乐器。每首歌都有很多艺术家。

所有的关系都是多对多的。如何实现这个？在歌曲实体和那三个类别实体之间建立多对多关系是个好主意吗？或者我应该只有一个继承这些子类别的单个类别实体？

提前感谢

I want to store information about song files which they are categorized by:
1. genre for example: pop, rock, classical etc.
2. instrument used like piano violinn etc
3. Artist
Each song has many instruments. Each song has many artist.
So all of the relations are many-to-many. How can I implement this? Is it good idea to make many-to-many relation between song entity and those three category type entities? or should I only have a single category entity that inherits those sub categories?
thanks in advance

推荐答案

乐器和艺术家都是has-a关系。类型是is-a关系的示例。 Has-a和Is-a关系在关系数据库设计中的建模截然不同。一首歌是流行歌曲，或一首歌是一首摇滚歌曲。 （我假设一首歌曲可以是流行和摇滚，但这不清楚在你的描述）。

The instrument and the artist are both examples of "has-a" relationships. The genre is an example of an "is-a" relationship. Has-a and Is-a relationships are modeled quite differently in relational database design. A song is a pop song, or a song is a rock song. (I presume that a song can be both pop and rock, but that's not clear in your description).

有一个关系更常见，通常覆盖任何好的教程。在这些教程中，关系往往没有得到足够的覆盖。如果你想得到一些关于is-a关系的ER建模的文章，查找泛化/专门化。

Has-a relationships are more common, and are generally covered in any good tutorial. Is-a relationships are often not given enough coverage in those same tutorials. If you want to get some articles about ER modeling of is-a relationships, lookup "generalization/specialization". This will explain how to depict one of these relationships but not how to design tables to fit.

如果你熟悉对象建模，你最容易的概念句柄是已知的作为类/子类模式aka类型/子类型。这是直接的，但它依赖于内置的支持继承。这种支持通常存在于任何对象系统中，并为您做大部分的艰苦工作。如果你正在设计一个关系数据库，你会面对面的关系模型不直接支持继承的事实。这意味着您必须设计模仿继承的好处的表，除非您愿意使用SQL方言中提供的SQL扩展。

If you are familiar with object modeling, your easiest handle on the concept is known as class/subclass pattern aka type/subtype. This is straightforward, but it depends on built in support for inheritance. This support is generally present in any object system, and does most of the hard work for you. If you are designing a relational database you come face to face with the fact that the relational model does not directly support inheritance. This means that you have to design tables that mimic the benefits of inheritance, unless you are willing to use SQL extensions available in your dialect of SQL.

这两个标签的名称已知两个相对标准的设计：
单表继承
class-table-inheritance

Two relatively standard designs are known by the names of these two tags: single-table-inheritance class-table-inheritance

您可以通过阅读每个标签的信息选项卡来获取简要说明。你可以通过查找Martin Fowler对这两种模式的处理得到更好的描述。

You can get a brief description by reading the Info tab of each of these tags. You can get an even better description by looking up Martin Fowler's treatment of these two patterns.

单一表格解决方案将帮助您为所有歌曲文件创建单个表格，其中包含与任何或所有类别相关的列。

The single table solution will help you create a single table for all song files, with columns that pertain to any or all of the categories.

类表解决方案将帮助您为歌曲文件设计一个表，为每个类型设计一个表。如果使用共享主键技术，从而导致流派表中的每个条目从歌曲文件表中的相应条目继承其ID，这将是有帮助的。你必须通过额外的编程，当你去插入一个新的歌曲文件，使这个继承发生。

The class table solution will help you design one table for the song files and one table for each genre. It will be helpful if you use the shared primary key technique and thereby cause each entry in a genre table to "inherit" its ID from the corresponding entry in the song file table. You have to make this inheritance happen by extra programming when you go to insert a new song file.

哪一个更好？这取决于你的情况。您决定。

Which one is better? It depends on your case. You decide.

这篇关于产品具有多个类别类型的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！