在MySQL中实现继承：替代和一个只有代理键的表 [英] Implementing inheritance in MySQL: alternatives and a table with only surrogate keys

问题描述

这是以前可能已经问过的问题，但是我很难找到完全符合我的情况，所以我将解释我的情况，搜索一些反馈：

我有一个应用程序将注册位置，我有几种类型的位置，每个位置类型有一组不同的属性，但我需要关联注释到位置，不管它们的类型和其他类型的内容（主要是多媒体条目和评论）。考虑到这一点，我想出了几个解决方案：

1. 为每个位置类型创建一个表，注释表，对于每个位置表使用外键，这是非常麻烦，因为我必须为每个注释表创建一个多媒体和注释表，例如：

   
   

   
   

   • LocationTypeA

     
     
     
     
     
     • ID
     
     
     • Attr1
     
     
     • 
       
     
     
   
   

   • LocationTypeA_Notes

     
     
     
     
     
     • ID
     
     
     • Attr1
     
     
     • ...
     
     
     • LocationTypeA_fk
     
     
   
   

   • LocationTypeA_Notes_Multimedia

     
     
     
     
     
     • ID
     
     
     
     
     ul>
     
     
     

   这样做会很讨厌，

创建一个表格，其中包含位置和点内容的唯一标识符，例如：







• 位置

  
  
  
  
  
  • ID
  
  



• LocationTypeA

  
  
  
  
  
  • ID
  
  
  • 
    
  
  
  
  

  • 注释

    
    
    
    
    
    • ID
    
    
    • Attr1
    
    
    • ...
    
    
    • Location_fk
    
    
  
  
  • p>多媒体
    
    
    
    
    
    • ID
    
    
    • Attr1
    
    
    • 。 ..
    
    
    • Notes_fk
    
    
  
  
  
  

正如你所看到的，这更简单，也更容易开发，但我只是不喜欢该表的外观只有ID（是的，这是真正唯一的反对，我有这个，




2. 与选项2类似，但我会有一个庞大的属性表格，如下所示： / p>
   
   

   
   

   
   

   • 位置

     
     
     
     
     
     • ID
     
     
     • 键入
     
     
   
   

   • 属性

     
     
     
     
     
     • 姓名
     
     
     • 价值
     
     
   / ul>
   

   等等，或每个属性的表;一个Drupal。这将是一个痛苦的发展，因为那将需要几个插入/更新操作来做某事在一个位置和属性表将比位置表大几倍（或结束了大量的属性表）;它也有同样的问题的surrogate-keys-only表（只是它有一个类型现在，我将用来定义的位置的编程方式的行为），但它是一个漂亮的解决方案。

因此，问题是：这将是一个更好的解决方案性能和可扩展性？提出？我没有执行任何这些问题，选项2和3将是一个有趣的发展，我从来没有做过这样的事情，但我不想与一个选项，将崩溃本身，当内容生长有点你可能会想到为什么不使用Drupal，如果你知道它的工作原理像你期望的？，我想你显然不知道使用Drupal有多困难，无论是或者一个专家，我绝对不是。

此外，现在我已经写了所有这些，你认为选项2是一个好主意吗？ ，你知道一个更好的方法来组实体/模拟继承？ （请不要说只是使用继承！，我只能使用MySQL）。

感谢您的反馈，对不起，如果我写

解决方案

ORM系统通常使用以下几种解决方案， p>

每个层次结构一个表

优点：

• 简单方法。


• 轻松添加新类，只需为其他数据添加新列。


• 只需更改行的类型即可支持多态性。


• 数据访问速度很快，因为数据在一个表中。


• 临时报告很容易，因为所有数据都在一个表中。

缺点：

• 类层次结构中的耦合增加，因为所有类都直接耦合到同一个表。




• 数据库中可能浪费的空间。


• 指示类型变得复杂，

使用：





• 对于简单和/或浅层类层次结构，这是一个好策略，其中层次结构中的类型之间很少或没有重叠。






每个具体类有一个表





优点：






• 易于执行即席报告，因为您需要的关于单个类的所有数据只存储在一个表中。


• 存取单一物件资料的成效良好。






缺点：






• 修改类时，需要修改其表和其任何子类的表。例如，如果要向Person类添加高度和权重，则需要向Customer，Employee和Executive表中添加列。


• 每当对象更改其角色时，雇用您的一个客户，您需要将数据复制到相应的表中，并为其分配一个新的POID值（或者您可以重用现有的POID值）。


• 很难支持多个角色，仍然保持数据完整性。例如，您要在哪里存储同时是客户和员工的人的姓名？






何时使用：






• 更改类型和/或类型之间重叠的情况很少见。



$ b b

每个表一个表





优点：






• 由于一对一映射，易于理解。


• 支持多态性，因为您只需在每个类型的相应表中具有记录。


• 只需修改/添加一个表即可轻松修改超类和添加新的子类。


• 数据大小与增长成正比






缺点：






• 数据库中有许多表，每个类有一个表（加上表来维护关系）。


• 使用此技术读取和写入数据可能需要更长时间，因为您需要访问多个表。如果通过将每个表放在不同物理磁盘驱动器盘片上的类层次结构内（这假设磁盘驱动器磁头都独立操作），您可以智能地组织数据库，从而缓解这个问题。


• 除非您添加视图以模拟所需的表格，否则很难对数据库进行即时报告。






何时使用：






• 当类型之间存在重大重叠或更改类型时很常见。






通用架构





优点：






• 当数据库访问由可靠的持久性框架封装时非常有效。


• 可以扩展为提供元数据广泛的映射，包括关系映射。简而言之，它是映射元数据引擎的开始。


• 它非常灵活，允许您快速更改存储对象的方式，因为您只需要更新元数据相应地存储在Class，Inheritance，Attribute和AttributeType表中。






缺点：


$ b b


• 非常先进的技术，最初难以实现。


• 它只适用于少量的数据，因为您需要访问许多数据库行构建单个对象。


• 您可能希望构建一个小型管理应用程序来维护元数据。


• 由于需要访问多行以获取单个对象的数据，因此针对此数据进行报表可能非常困难。






何时使用：






• 对于使用少量数据的复杂应用程序，您的数据访问不常见，或者您可以将数据预加载到缓存中。





This is a question that has probably been asked before, but I'm having some difficulty to find exactly my case, so I'll explain my situation in search for some feedback:

I have an application that will be registering locations, I have several types of locations, each location type has a different set of attributes, but I need to associate notes to locations regardless of their type and also other types of content (mostly multimedia entries and comments) to said notes. With this in mind, I came up with a couple of solutions:

1. Create a table for each location type, and a "notes" table for every location table with a foreign key, this is pretty troublesome because I would have to create a multimedia and comments table for every comments table, e.g.:

   • LocationTypeA

     • ID
     • Attr1
     • Attr2

   • LocationTypeA_Notes

     • ID
     • Attr1
     • ...
     • LocationTypeA_fk

   • LocationTypeA_Notes_Multimedia

     • ID
     • Attr1
     • ...
     • LocationTypeA_Notes_fk

   And so on, this would be quite annoying to do, but after it's done, developing on this structure should not be so troublesome.

2. Create a table with a unique identifier for the location and point content there, like so:

   • Location

     • ID

   • LocationTypeA

     • ID
     • Attr1
     • Attr2
     • Location_fk

   • Notes

     • ID
     • Attr1
     • ...
     • Location_fk

   • Multimedia

     • ID
     • Attr1
     • ...
     • Notes_fk

   As you see, this is far more simple and also easier to develop, but I just don't like the looks of that table with only IDs (yeah, that's truly the only objection I have to this, it's the option I like the most, to be honest).

3. Similar to option 2, but I would have an enormous table of attributes shaped like this:

   • Location

     • ID
     • Type

   • Attribute

     • Name
     • Value

   And so on, or a table for each attribute; a la Drupal. This would be a pain to develop because then it would take several insert/update operations to do something on a location and the Attribute table would be several times bigger than the location table (or end up with an enormous amount of attribute tables); it also has the same issue of the surrogate-keys-only table (just it has a "type" now, which I would use to define the behavior of the location programmatically), but it's a pretty solution.

So, to the question: which would be a better solution performance and scalability-wise?, which would you go with or which alternatives would you propose? I don't have a problem implementing any of these, options 2 and 3 would be an interesting development, I've never done something like that, but I don't want to go with an option that will collapse on itself when the content grows a bit; you're probably thinking "why not just use Drupal if you know it works like you expect it to?", and I'm thinking "you obviously don't know how difficult it is to use Drupal, either that or you're an expert, which I'm most definitely not".

Also, now that I've written all of this, do you think option 2 is a good idea overall?, do you know of a better way to group entities / simulate inheritance? (please, don't say "just use inheritance!", I'm restricted to using MySQL).

Thanks for your feedback, I'm sorry if I wrote too much and meant too little.

解决方案

ORM systems usually use the following, mostly the same solutions as you listed there:

One table per hierarchy

Pros:

• Simple approach.
• Easy to add new classes, you just need to add new columns for the additional data.
• Supports polymorphism by simply changing the type of the row.
• Data access is fast because the data is in one table.
• Ad-hoc reporting is very easy because all of the data is found in one table.

Cons:

• Coupling within the class hierarchy is increased because all classes are directly coupled to the same table.
• A change in one class can affect the table which can then affect the other classes in the hierarchy.
• Space potentially wasted in the database.
• Indicating the type becomes complex when significant overlap between types exists.
• Table can grow quickly for large hierarchies.

When to use:

• This is a good strategy for simple and/or shallow class hierarchies where there is little or no overlap between the types within the hierarchy.

One table per concrete class

Pros:

• Easy to do ad-hoc reporting as all the data you need about a single class is stored in only one table.
• Good performance to access a single object’s data.

Cons:

• When you modify a class you need to modify its table and the table of any of its subclasses. For example if you were to add height and weight to the Person class you would need to add columns to the Customer, Employee, and Executive tables.
• Whenever an object changes its role, perhaps you hire one of your customers, you need to copy the data into the appropriate table and assign it a new POID value (or perhaps you could reuse the existing POID value).
• It is difficult to support multiple roles and still maintain data integrity. For example, where would you store the name of someone who is both a customer and an employee?

When to use:

• When changing types and/or overlap between types is rare.

One table per class

Pros:

• Easy to understand because of the one-to-one mapping.
• Supports polymorphism very well as you merely have records in the appropriate tables for each type.
• Very easy to modify superclasses and add new subclasses as you merely need to modify/add one table.
• Data size grows in direct proportion to growth in the number of objects.

Cons:

• There are many tables in the database, one for every class (plus tables to maintain relationships).
• Potentially takes longer to read and write data using this technique because you need to access multiple tables. This problem can be alleviated if you organize your database intelligently by putting each table within a class hierarchy on different physical disk-drive platters (this assumes that the disk-drive heads all operate independently).
• Ad-hoc reporting on your database is difficult, unless you add views to simulate the desired tables.

When to use:

• When there is significant overlap between types or when changing types is common.

Generic Schema

Pros:

• Works very well when database access is encapsulated by a robust persistence framework.
• It can be extended to provide meta data to support a wide range of mappings, including relationship mappings. In short, it is the start at a mapping meta data engine.
• It is incredibly flexible, enabling you to quickly change the way that you store objects because you merely need to update the meta data stored in the Class, Inheritance, Attribute, and AttributeType tables accordingly.

Cons:

• Very advanced technique that can be difficult to implement at first.
• It only works for small amounts of data because you need to access many database rows to build a single object.
• You will likely want to build a small administration application to maintain the meta data.
• Reporting against this data can be very difficult due to the need to access several rows to obtain the data for a single object.

When to use:

• For complex applications that work with small amounts of data, or for applications where you data access isn’t very common or you can pre-load data into caches.

这篇关于在MySQL中实现继承：替代和一个只有代理键的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！