如何在数据库中实现超类、子类关系?[英] How to implement a super class, sub class relationship in the database?

本文是小编为大家收集整理的关于如何在数据库中实现超类、子类关系?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

如果我有一个叫做动物,狗和鱼类的课程,则是子类. 该动物的属性称为"颜色". 狗具有称为"尾部长度"的属性,而鱼则没有这种属性. 鱼具有称为"重量"的属性,狗没有此属性.

所以,我想设计一个数据库来存储此信息.我应该怎么办?这是一些想法:

想法1: 制作动物桌,桌子有类型,以找到哪种动物,如果是狗,只需从狗桌上得到结果即可.

动物: 颜色:字符串 类型:int

类型: 狗:0 鱼:1

狗: taillength:int

鱼: 重量:int

想法2: 在数据库中仅存放狗桌和鱼桌,卸下动物桌.

狗: 颜色:字符串 taillength:int

鱼: 颜色:字符串 重量:int

推荐答案

您提到的两种方法:

  • 一个表代表整个继承层次结构中对象的表,整个层次结构以及"类型"列所需的所有列,以告诉您哪个特定对象是哪个子类.
  • 每个混凝土类的一张表格在您的继承层次结构中,带有重复的模式.

可以补充另外两个:

  • 继承层次结构中的每个类别的一个表 - 您现在有一个 Animal 表,子类有外键的表,这些表指向 Animal 表>.
  • 通用模式 - 有一个用于存储对象的表,以及一个属性表,以支持该对象附加的任何属性集.

每种方法都有利弊.这里有一个很好的破产:

也看一下这些主题:

最后,应该注意的是,存在面向对象的数据库(又称对象数据库或OODBMSES),在数据库中更自然地表示对象,尽管可以轻松地解决此问题,尽管我认为它们在行业中的经常使用.以下是一些与关系(和其他)DBS相比的DBS的链接,尽管它们不会给您完全客观(heh)关于此事的观点:

其他推荐答案

您可以这样尝试:

Animal
    PK animal_id
    FK animal_type
    STRING animal_name (eg. 'Lassie')

AnimalTypes
    PK animal_type
    STRING animal_type_name (eg. 'Dog')

AnimalAttributes
    PK attribute_id
    STRING attribute_name (eg. 'tail length')

AnimalToAttributes
    PK id
    FK animal_id
    FK attribute_id
    INTEGER value (eg. 20)

这样,您可以拥有每只动物的一个或多个属性(取决于您).

其他推荐答案

在您注意到时,请使用一个到零或一个关系,在数据库架构设计语言中,表称为类 - 子类或超类

   Create Table Animal
    (animalId Integer Primary Key Not null,
     Other columns generic to all animals)

   Create Table Birds
    (BirdId Integer Primary Key Not Null 
     references Animal(AnimalId),
     -- other columns)

本文地址:https://www.itbaoku.cn/post/597747.html

问题描述

If I have a class called animal, dog and fish is the subclass. The animal have attribute called "color". Dog have the attribute called "tail length", and the fish don't have this attribute. Fish have the attribute called "weight", the dog don't have this attribute.

So, I want to design a database to store this information. What should I do? Here is some ideas:

Idea 1: Making an animal table, and the table have type, to find what kind of animal, if it is a dog, just get the result from dog table.

Animal: color:String type:int

Type: Dog:0 Fish:1

Dog: TailLength:int

Fish: Weight:int

Idea 2: Store only Dog table and Fish table in the database, remove the animal table.

Dog: Color: String TailLength: int

Fish: Color: String Weight: int

推荐答案

The two approaches you mentioned:

  • One table representing objects in the entire inheritance hierarchy, with all the columns you'd need for the entire hierarchy plus a "type" column to tell you which subclass a particular object is.
  • One table for each concrete class in your inheritance hierarchy, with duplicated schema.

can be supplemented by two others:

  • One table for each class in your inheritance hierarchy – you now have an Animal table, and subclasses have tables with foreign keys that point to the common set of data in Animal.
  • Generic schema – have a table to store objects, and an attribute table to support any set of attributes attached to that object.

Each approach has pros and cons. There's a good rundown of them here:

Also take a look at these SO topics:

Finally, it should be noted that there are object-oriented databases (aka object databases, or OODBMSes) out there that represent objects more naturally in the database, and could easily solve this problem, though I don't think they're as frequently used in the industry. Here are some links that describe such DBs as compared to relational (and other) DBs, though they won't give you an entirely objective (heh) view on the matter:

其他推荐答案

You could try it like this:

Animal
    PK animal_id
    FK animal_type
    STRING animal_name (eg. 'Lassie')

AnimalTypes
    PK animal_type
    STRING animal_type_name (eg. 'Dog')

AnimalAttributes
    PK attribute_id
    STRING attribute_name (eg. 'tail length')

AnimalToAttributes
    PK id
    FK animal_id
    FK attribute_id
    INTEGER value (eg. 20)

This way you can have one or many attributes per animal (it's up to you to choose).

其他推荐答案

Use a one to zero or one relationship As you note, In database schema design language the tables are called class - sub-class or superclass

   Create Table Animal
    (animalId Integer Primary Key Not null,
     Other columns generic to all animals)

   Create Table Birds
    (BirdId Integer Primary Key Not Null 
     references Animal(AnimalId),
     -- other columns)