Could I same column to represent a foreign key to multiple tables?

Question

I am trying to use the same column to represent a has foreign key to different columns. This is because there could be an arbitrary number of tables to be indexed using this column.
Right now, my idea is to use a small varchar() field to represent which field they are indexing and then check for them my probably sub-querying for all that match the given field, then querying based on the id?
Is this a good method that would take advantage of MySQL indexing?
Are there any other better ways to accomplish this?

Answer 1

Check out the http://github.com/Theaxiom/Polymorphic2.0 Polymorhpic Behavior.

You use 2 fields to represent a connection to any other table. One field holds the ModelName of the linked Model and the other holds any arbitrary foreign_id value.

Answer 2

Create a "supertype" table that unifies the keys from the other tables. This example might help:

http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

Answer 3

I usually use Abba's solution for these sort of problems in a one-to-many relationship. Use a type field to define the table the foreign key reffers to.

If this comes up in a one-to-one relationship you may consider flipping the relationship around. Move the foreign key to the other tables. Any number of tables may link a foreign key to the single original table.

Answer 4

One way to represent the gen-spec design pattern is to use the same key as both a foreign key and as a primary key in the specialized tables. As a foreign key, it references the PK in the generalized table. And the PK in the generalized table references a row in one of the specialized tables, without specify which one.

This is the usual method of modeling the gen-spec pattern in the relational model.