如何在MS Access中创建(类型，ID)(又名“多态")外键列? [英] How do I create a (Type, ID) (aka 'polymorphic')- foreign key column in MS Access?

问题描述

在Ruby-on-Rails中，这称为多态关联".

In Ruby-on-Rails, this is called a "polymorphic association."

我的应用程序中有几个Commentable内容，每个表格都在下面:

I have several Commentable things in my application, the tables for each are below:

Post
id | title | text | author (FK:Person.id) | ...

Person
id | name | ...

Photo
id | title | owner (FK:Person.id) | path | ...

我想添加一个Comments表，如下所示:

I'd like to add a Comments table as follows:

Comments
id | commentable_type | commentable_id | text | author (FK:Person.id)

我知道我会丢失数据库的参照完整性，但是唯一的选择是拥有多个Comments表:PostComments，PersonComments，PhotoComments，...

I understand that I lose the database's referential integrity this way, but the only other option is to have multiple Comments tables: PostComments, PersonComments, PhotoComments, ...

现在是这个问题:

如何通过首先从Comments.commentable_type获取表名，然后从Comments.commentable_id获得ID的方式来构建将如何进行查找的表单?

How can I build a form that will grok how to do the lookup, first by getting the table name from Comments.commentable_type and then the id from Comments.commentable_id?

推荐答案

此技术在SQL世界中俗称子类化".有关有效的示例(SQL Server语法，但很容易适用于MS Access)，请参见

This technique is known colloquially in the SQL world as 'subclassing'. For a worked example (SQL Server syntax but is easily adapted for MS Access), see David Porta's blog..

在您的方案中，所有注释共有的数据项将在注释"表中；注意，FK应该是ID加上类型的ID的两列化合物，通常会被忽略，但对于此处的引用完整性必不可少.例如，FK应该是ID的两列化合物.您不希望在PersonComments表中出现键入照片评论的内容.

In your scenario, the data items common to all comments would be in your Comments table; anything specific to each type would be in specialized tables such as PhotoComments, etc. Note the FK should be the two-column compound of the ID plus the type, something which is often overlooked but is essential to referential integrity here e.g. you don’t want something typed as a photo comment appearing in the PersonComments table.

这篇关于如何在MS Access中创建(类型，ID)(又名“多态")外键列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！