CODEWITHSUNDEEP
mysqlsqldatabase-design
Ismail
Ismail

Reputation: 9592

Is it possible to have an dynamic foreign key, and what is the best/correct to do so?

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

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

Upvotes: 9

Views: 8284

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

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

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

Upvotes: 15

Related Questions