表设计和类层次结构 [英] Table design and class hierarchies

问题描述

希望有人可以通过一个例子或者一些建议的阅读来阐述这个问题。 我想知道在类层次结构等价物之后，建模表的最佳设计方法是什么？这可以通过一个例子来描述：

 抽象类卡{
 private $ _name =''; 
 private $ _text =''; 
} 
 
 class MtgCard扩展卡{
 private $ _manaCost =''; 
 private $ _power = 0; 
 private $ _toughness = 0; 
 private $ _loyalty = 0; 
} 
 
 class PokemonCard扩展卡{
 private $ _energyType =''; 
 private $ _hp = 0; 
 private $ _retreatCost = 0; 
} 
  

现在，当建模表与此类层次结构同步时，我已经走了有一些非常相似的东西：

  TABLE卡
 id INT，AUTO_INCREMENT，PK 
名称VARCHAR（255） 
文本TEXT 
 
表MtgCard 
 id INT，AUTO_INCREMENT，PK 
 card_id INT，FK（card.id）
 manacost VARCHAR（32）
电源INT 
韧性INT 
忠诚INT 
 
表PokemonCard 
 id INT，AUTO_INCREMENT，PK 
 card_id INT，FK（card.id）
 hp INT 
能量类型ENUM（...）
 retreatcost INT 
  

我遇到的问题是试图找出如何将每个卡记录与包含相应表格的记录相关联。具体来说，如何确定我应该查找哪个表。

我应该将一个 VARCHAR 列添加到卡保存关联表的名称？这是我同龄人和我来的唯一决议，但它似乎也是脏的。 保持设计可扩展性是这里的关键，允许轻松添加新的子类。

如果有人可以提供一个示例或资源清晰的镜像类/表层次的方法，这将是最受赞赏的。

解决方案

Google泛化专业化关系建模。你会发现几个关于如何使用关系表模拟基因规范模式的优秀文章。这个同样的问题在SO中被多次提到，细节略有不同。

最好的这些文章将确认您决定为广义数据提供一个表格，并为专门的数据分别提供表格。最大的区别是他们建议使用主键和外键的方式。基本上，他们建议专门的表格有一个单一的列执行双重任务。它是专业表的主要关键，但它也是重复广义表的PK的外键。

维护有点复杂，但是在加入时非常甜蜜。

还要记住，当将一个新类添加到层次结构中时，需要DDL。

Hopefully someone can shed some light on this issue through either an example, or perhaps some suggested reading. I'm wondering what is the best design approach for modeling tables after their class hierarchy equivalencies. This can best be described through an example:

abstract class Card{
    private $_name = '';
    private $_text = '';
}

class MtgCard extends Card{
    private $_manaCost = '';
    private $_power = 0;
    private $_toughness = 0;
    private $_loyalty = 0;
}

class PokemonCard extends Card{
    private $_energyType = '';
    private $_hp = 0;
    private $_retreatCost = 0;
}

Now, when modeling tables to synchronize with this class hierarchy, I've gone with something very similar:

TABLE Card
  id            INT, AUTO_INCREMENT, PK
  name          VARCHAR(255)
  text          TEXT

TABLE MtgCard
  id            INT, AUTO_INCREMENT, PK
  card_id       INT, FK(card.id)
  manacost      VARCHAR(32)
  power         INT
  toughness     INT
  loyalty       INT

TABLE PokemonCard
  id            INT, AUTO_INCREMENT, PK
  card_id       INT, FK(card.id)
  hp            INT
  energytype    ENUM(...)
  retreatcost   INT

The problem I'm having is trying to figure out how to associate each Card record with the record containing it's details from the corresponding table. Specifically, how to determine what table I should be looking in.

Should I add a VARCHAR column to Card to hold the name of the associated table? That's the only resolution that my peers and I have come to, but it seems too "dirty". Keeping the design extensible is the key here, allowing for the easy addition of new subclasses.

If someone could provide an example or resources showing a clean way of mirroring class/table hierarchies, it would be most appreciated.

解决方案

Google "generalization specialization relational modeling". You'll find several excellent articles on the subject of how to model the gen-spec pattern using relational tables. This same question has been asked many times in SO, with slightly different details.

The best of these articles will confirm your decision to have one table for generalized data and separate tables for specialized data. The biggest difference will be the way they recommend using primary and foreign keys. Basically, they recommend that specialized tables have a single column that does double duty. It serves as the primary key to the specialized table, but it's also a foreign key that duplicates the PK of the generalized table.

This is a little complicated to maintain, but it's very sweet at join time.

Also keep in mind that DDL is required when a new class is added to the hierarchy.

这篇关于表设计和类层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！