How to create a column (Type, ID) (aka 'polymorphic') - foreign key in MS Access?

Question

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

I have a few Commentable things in my application, tables for each below:

 Post id | title | text | author (FK:Person.id) | ... Person id | name | ... Photo id | title | owner (FK:Person.id) | path | ... 

I would like to add a Comments table as follows:

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

I understand that I have lost the referential integrity of the database this way, but the only option is to have several Comments : PostComments , PersonComments , PhotoComments , ... tables

And now for the question:

How can I create a form that will check how to perform a search by first getting the table name from Comments.commentable_type and then the identifier from Comments.commentable_id ?

Answer 1

This method is commonly known in the SQL world as a “subclassification”. For a processed example (SQL Server syntax, but easily adapted for MS Access), see David Porta blog .

In your scenario, the data elements common to all comments will be in your comment table; anything specific for each type would be in specialized tables such as PhotoComments, etc. Note that FK should be a two-column identifier mix plus type, which is often overlooked but important for referential integrity here, for example. You don’t want something to be entered as a comment on a photo in the PersonComments table.

Answer 2

I believe that many people make meta tables for this kind of thing. To a large extent, as you described it.