9

For example: I have a "history" table with a foreign key that might point to different tables/entities depending a field value.

Table history:

create table history(
    id int PimaryKey AUTO_INC,
    elementid int, (ForeignKey)
    elementtype varchar
)

this table is populated for example:

id               ElementId           ElementType
1                1                   Device
2                2                   Simcard
3                2                   Simcard

this tells we have

  • 1 record for entity of type "Device" with id "1"
  • 2 records for entity of type "Simcard" with id "2"

the goal is to have a table that might be used for multiple entities.

Is this the correct way to integrate an dynamic foreign key? What I also thought of was for creating an list with tables/entities to refer to So the table then would look like:

ALTER TABLE history MODIFY ElementType INTEGER;

ElementType would refer to:

create table entities(
    id int PimaryKey AUTO_INC,
    name varchar
)

the new table history is populated for example:

id               ElementId           ElementType
1                1                   1
2                2                   2
3                2                   2

The entities table is populated for example:

id               name
1                Device
2                Simcard
Ismail
  • 8,904
  • 3
  • 21
  • 39
  • Is it a coincidence that the `elementid` of 2 equals a simcard? Reason I ask is that if that would always be the case, the `elementid` would already identify the element as a simcard, and thus the `elementtype` column would be redundant. – SchmitzIT Mar 13 '14 at 08:19
  • @SchmitzIT no. The elementId refers to the record id of the entity that is filled in elementType. For example I have an entity simcard with a couple instances/rows I want to have record id 3 of entity simcard, so this wil be an instance of history with: elementid = 3 elementType= simcard ElementType refers to the entity – Ismail Mar 13 '14 at 08:23

1 Answers1

13

The proper way to implement dynamic FKs is to not do it.

Here are the reasons why, and what to do instead.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167