Sql - indirect foreign key

Question

I have some questions about database design.

• Is there a name for this?
• Is this a good practice?
• Any performance considerations?

I have a general table structure used to store relationships.

I recently reworked some things to use this general structure instead of direct Fk columns, but now I'm not sure if this was really a better idea.

Original circuit:

  + ------------------ + + --------------------- + + ------ ---------------- +
  |  Book |  |  Note |  |  MetaParent |
  | ------------------ |  | --------------------- |  | ---------------------- |
  |  Id |  |  Id |  |  Id |
  |  NoteId |  |  MetaParentId: (Null) |  |  MetaTableId |
  |  + ------- + + ---- + KeyValue |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  + ------------------ + + --------------------- + + ------ ---------------- + 

New scheme

  + ------------------ + + --------------------- + + ------ ---------------- +
  |  Book |  |  Note |  |  MetaParent |
  | ------------------ |  | --------------------- |  | ---------------------- |
  |  Id |  |  Id |  |  Id |
  |  |  |  MetaParentId: (Null) |  |  MetaTableId |
  |  + + + ---- + KeyValue |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  |  |  |  |  |  |
  + ------------------ + + --------------------- + + ------ ---------------- + 

So basically instead of the direct Fk relationship between Book and Note, we have an indirect relationship through the MetaParent table using the MetaTableId / KeyValue columns.

Currently, the MetaParent table contains about 500 thousand records, and everything works fine. But we restore indexes every night.

My fears are that the relationship between the book and the record is not obvious. You need to know what exists and use the MetaParent table.

Also performance, I'm not sure at what point we would encounter problems merging with MetaTableId / KeyValue, which run too slowly. It seems the more you add to this table, the slower the queries will be.

Answer 1

You should always apply referential integrity using the "regular" FOREIGN KEYS.

In short, FOREIGN KEYs have the following advantages:

• They are already implemented in the DBMS.
• They are declarative, self-documenting and “obvious”.
• They cannot be bypassed (unless they are explicitly disabled or deleted).
• They are true.
• They are fast.
• They support cascading reference actions (for example, ON DELETE CASCADE).
• The DBMS knows that the data is connected, which allows you to find the best query plan in some cases .
• If you use the ORM tool, it can automatically create links between objects.

And here are the following disadvantages of providing referential integrity in application code:

• You are duplicating work that has already been completed.
• This is necessary, perhaps “buried” deep in the source code of the application, and more difficult to maintain.
• The only client application that has an error can break referential integrity (and corrupt data).
• You are probably implementing them incorrectly in your application code. It looks simple from the start, but in a parallel environment it's easy to enter race conditions .
• Even if you implemented them correctly, you probably used some form of locking to avoid race conditions that are likely to be slower / less scalable than the specially optimized FKs built into the DBMS.
• You must implement cascading yourself.
• The DBMS does not know what data is connected, which can lead to the creation of a suboptimal query plan.
• You may need to do more manual work in your ORM tool.

---

Is there a name for this?

Not that I knew. I heard that the term “generic FK” is used, but probably not universal.

Is this a good practice?

No (see above).

Any performance considerations?

Yes (see above).