I have the following problem.

I currently have several tables, e.g. Foo, Bar, Foobar, and several affiliated tables, e.g. Foo_Attribute, Bar_Attribute, Foobar_Attribute.

The attribute tables essentially have a name column and a value column and a FK reference to the affilated table (which should be obvious via the names).

These attribute tables provide some flexibility to add new attributes dynamically so we don't have to hardcode them in the schema of the affiliated FK table(think of them as possibly transient properties).

The problem is that I have alot of business layer code that works with with these attributes and I have to essentially write (and maintain) the same boiler plate code multiple times; I'm hate duplication so this makes me uneasy.

What I'd really rather have is a generic attribute table with owner_id, owner_id_type columns so that the foreign key reference can be mapped dynamically to the appropriate owner table based on the id_type column(e.g. id=2, id_type='FOO' maps as a FK to the Foo table where Foo.id=2).

I've looked through some inheritance examples but I really haven't found a similar case to this where the FK table ASSOCIATION is polymorphic.

I'd appreciate any pointers from someone who has solved this problem before.

Charles