1

I want to implement a generic table say, info, with column type and foreign_id. foreign_id is the foreign key to a different table depending the type.

With Doctrine ORM, my understanding is that you need to specify the mapping relations in advance. However, since foreign_id can link to different tables depending on the type. How do you implement this kind of relations using ORM?

Currently, I get around the issue by creating multiple tables, say info_a, info_b, with a_id and b_id. If the number of types increases, it'll result in a lot of tables that have essentially similar structures.

How do you resolve this using ORM? Thanks in advance.

Mr. 14
  • 9,228
  • 6
  • 37
  • 54
  • 2
    Having a foreign key mapped to multiple tables is an anti-pattern in database design. See [here](http://stackoverflow.com/questions/22177776/foreign-key-column-mapped-to-multiple-primary-keys/22179608#22179608). – Mohsen Heydari Nov 24 '14 at 17:55

2 Answers2

3

You "resolve" this by accepting the fact that you will have multiple tables "with similar structures".

The reason for which you have tables is not their structure, the reason why you have tables is because of what they (and their contents) mean to the end user.

So the decision about which number of tables to have, should be driven first and foremost by what meaning they represent to the user, with distinct types of meaning resulting in distinct tables. The habit of observing that two tables "have the same structure" and therefore should be merged into one is widespread, but it is a serious mistake nonetheless.

Do yourself a favour and learn/adopt the reflex that "generic tables" are BAD.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • Thanks for your reply. I think it's good to keep your code DRY. Shouldn't that apply to database structure as well? – Mr. 14 Nov 27 '14 at 10:02
  • No. Database structure is not code. See http://www.quora.com/How-would-you-explain-the-relational-database-model/answer/Jan-Hidders?share=1 . The "structure" of a table is the "set of holes" mentioned in there. But you can have multiple distinct predicates (= multiple distinct external meanings) with exactly the same "set of holes". – Erwin Smout Nov 27 '14 at 13:10
0

You don't have multiple foreign keys. You have things reminiscent of FKs. A FK is a column set whose subtype values are always also in another table where the columns form a candidate key. As commented your design is an anti-pattern because it is needlessly complicated. And SQL doesn't easily support its integrity or its optimization.

Instead of having columns type and foreign_id [sic] in table info where type value indicates which other table that it must appear in as an id value it is straightforward to have a table VALUE_info for each value permitted in type with a FK foreign_id to the appropriate VALUE_other table id.

However it's possible that what your really need is each VALUE_other table to have its foreign_id column be a FK referencing info. Ie you might want the FK going the other way. This happens when info(id,type,...) means "thing [id] is of subtype [type] and ...) and VALUE_other(foreign_id,...) means "thing [foreign_id] is of subtype VALUE and ...". Notice that the proper names are then thing_info(id,type,...) and VALUE_thing_info(id,...). You might not want type any more. Things similar to this might all be set up automatically by an ORM that has explicit support for "subtyping" or "polymorphism".

If this suits your needs then in this particular case of subtyping since you were able to have every row of info have a foreign_id FK to one VALUE_other table it means that every thing is of exactly one subtype. For at most one subtype you can write an SQL constraint for each VALUE_thing_info table that an id's matching row in thing_info has a type value of VALUE. (Technically type is still redundant in the database but it can help with clarity and efficiency.) You can write an SQL constraint that the thing_info id values are in the union of the VALUE_thing_info id values. (Some such constraints can be declarative. Having type in thing_info helps with that. Putting type also in the VALUE_thing_info tables is another more redundant idiom that however can also help being more declarative. Although constraints on updates can be faster.)

philipxy
  • 14,867
  • 6
  • 39
  • 83