关系数据库中的面向对象的结构 [英] Object-oriented-like structures in relational databases

问题描述

人们，

对于连续的第n次，我再次遇到同样的老问题。它是关于如何以无痛方式将OOP结构映射到数据库表。

For the n-th time in a row, i'm hitting the same old problem again. It's about "how do I map OOP structures to database tables in a painless way."

这里有一个场景：我的系统中有几种类型的actors ，雇主，联系人。他们有一些共同的功能;其他部分是非常不同的。所有参与者处理的实体是通信，笔记（管理员喜欢留下客户的笔记）等等。有各种类型的其他实体，每个actor类型处理，而其他的没有。

Here's a scenario: I have several types of "actors" in my system - workers, employers, contacts. They have certain pieces of functionality in common; other pieces are vastly different. The entities that all actors deal with are "communications", "notes" (admins like to leave notes on customers), and a few more. There are tons of types of other entities that each actor type deals with, while the others don't.

目前，我的数据库模式包含以下表格：

Currently, my database schema includes tables for:

演员：

• 工人


• 雇主


• 联系

实体：

• 沟通


• li>
  
• 等。

实体和演员之间的关联表：

Association tables between entities and actors:

• 工人沟通协会


• 雇主沟通协会


• worker-notes-assn


• 等等，你得到了钻。

• worker-communication-assn
• employer-communication-assn
• worker-notes-assn
• etc, you get the drill.

这对我来说就像是一个代码气味。每当客户改变他们的角色（即从联系人升级到雇主），需要运行一堆疯狂的脚本。 Yuck ...另一方面，如果我在一个纯粹的OOP驱动的世界中操作，这将更容易 - 有一个基类的所有实体具有共同的属性，并完成它...

This feels like a "code smell" to me. Whenever a customer changes their role (i.e. promoted from "contact" to "employer"), a bunch of crazy scripts need to be run. Yuck... On the other hand, if i was operating in a purely OOP-driven world, this would be much easier - have a base class for all entities with common properties, and be done with it...

在DB世界里，这个选项在理论上是可能的，但听起来很乱。如果我理解这个权利，我会有一个新的base_actor表，并且每个其他actor将有一个base_actor_id，然后关联将在base_actor和实体之间......但是，我怎么做反向关联查询？也就是说告诉我所有的通信只是类型工人的行动者？

In the DB world, that option seems theoretically possible, but sounds very messy... I.e. if I understand this right, I'd have a new base_actor table, and each other actor would have a base_actor_id, and then the associations would be between base_actor and the entities... But then, how do I do reverse-association queries? I.e. "show me all communications with just actors of type worker"?

任何建议？任何关于映射OOP结构到关系DB的一般想法？

Any advice? Any general thoughts on the subject of "mapping OOP structures to relational DB"?

推荐答案

这是我在大约10年前提出的解决方案。使用这种设计的系统仍在运行，所以它运行得很好，比我的大多数代码生存时间更长。 ;）今天我可以使用Scott提到的ORM包之一，但是直接使用SQL确实没有很大的问题。

Here's a solution I came up with about 10 years ago. The system that uses this design is still running, so it worked well enough to survive longer than most of my code. ;) Today I may use one of the ORM packages that Scott mentions, but there's really no huge problems just using SQL directly.

1. 将所有的继承关系建模为表之间的连接。系统中的每个表都包含特定类的属性。

1. Model all of your inheritance relations as joins between tables. Each table in your system will hold the attributes of a specific class.

使用合成对象标识（oid）作为所有对象的主键。

Use a synthetic object id (oid) as your primary key for all objects. A sequence generator or autoincrement column is necessary to generate oid values.

所有继承的类必须使用与其父类相同的oid类型。将oid定义为具有级联删除的外键。

All inherited classes must use the same oid type as their parent. Define the oid as a foreign key with cascaded delete. The parent table gets the autoincrement oid column and the children get plain oid columns.

最终类的查询在相应的表上进行。您可以将所有父类表连接到查询中，或者只是延迟加载所需的属性。如果你的继承层次结构很深，并且你有很多类，一个ORM包真的可以简化你的代码。我的系统有不到50个类，最大继承深度为3.

Queries on final classes are made on the corresponding table. You can either join all the parent class tables into the query or just lazy load the attributes you need. If your inheritance hierarchy is deep and you have many classes, an ORM package can really simplify your code. My system had less than 50 classes with a maximum inheritance depth of 3.

跨子类（即父类的查询）的查询可以延迟加载子属性，或者您可以对与基类连接的每个子类重复查询。基于父类查询的延迟加载子属性需要您知道对象的类型。您可能已经在父类中有足够的信息，但如果不是，您将需要添加类型信息。

Queries across child classes (i.e. queries on a parent class) can either lazy load the child attributes on a per-instance basis, or you can repeat the query for each child class joined with base classes. Lazy loading child attributes based on a parent class query requires you know the type of the object. You may have enough information in the parent classes already, but if not you'll need to add type information. Again, this is where an ORM package can help.

在表结构中可以跳过没有成员属性的虚拟类，但你不能根据这些类进行查询。

Virtual classes without member attributes can be skipped in the table structure, but you won't be able to query based on those classes.

这里是显示所有与类型worker的演员的通信看起来像。 >

Here's what "show me all communications with just actors of type worker" looks like.

select * from comm c, worker w where c.actor=w.oid;

如果你有通信的子类，并且你想立即加载所有的子类属性也许你的系统不允许部分构建），最简单的解决方案是在所有可能的类上热切加入。

If you have sub-classes of communication, and you want to immediately load all the child class attributes (perhaps your system does not allow partial construction), the easiest solution is to eager join on all the possible classes.

select * from comm c, worker w, missive m where c.actor=w.oid and c.oid=m.oid;
select * from comm c, worker w, shoutout s where c.actor=w.oid and c.oid=s.oid;

最后一件事。确保您有一个好的数据库和正确的索引。如果数据库无法优化这些联接，性能可能是一个严重的问题。

One last thing. Make sure you have a good database and correct indexes. Performance can be a serious problem if you database can't optimize these joins.

这篇关于关系数据库中的面向对象的结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！