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?
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.
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.
Create a “supertype” table that unifies the keys from the other tables. This example might help:
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.