1

There are several independent tables, table1, table2, table3 etc., each of which has a primary key. In addition, there is also a kinda shared table shared_table with 3 columns - text_value, fake_foreign_key and table_name, it is a called shared_table in that each of the independent tables (table1, table2, table3) needs to join to the shared_table by using the column fake_foreign_key and the table_name to get some data from the same column named text_value.

Based on the design above, it is way difficult to join an independent table with the shared_table directly with SQL, but of course with the help of some programming language it is possible.

In relational database design, based on the ER modeling, there should be relationship between two relational tables. So I am surprised about the design above, as there is actually no direct primary key and foreign key relationship between the shared_table and other tables. Moreover, the mapping of the table has to be computed programmatically, and thus it will be difficult to use the ORM (object-relational mapping) framework.

The mere benefit for this design is the amount of tables is much smaller than in the case if we create separate text value tables for each of the reference table, i.e. table1, table2, table3, as all the text values are in the same table shared_table

Question: is this design an anti-pattern? shall we really need such design?

Rui
  • 3,454
  • 6
  • 37
  • 70
  • Possible duplicate of [Foreign Key to multiple tables](https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables) – philipxy Jun 21 '18 at 04:57
  • A FK just tells the DBMS that subrows must appear elsewhere. If not, no FK. Tables represent relation(ship)s/associations & queries generate new ones from given ones. Re your design, this is a faq, google re database/sql subtyping/inheritance/polymorphism & its antipattern multiple/many FK(s) to multiple/many tables. (table_name = subtype.) Always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. If you don't find an answer post one variant search for your title & keywords for your tags. – philipxy Jun 21 '18 at 05:00
  • 1
    Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what can't be given in text. And never give a diagram without a legend/key. – philipxy Jun 21 '18 at 05:00

3 Answers3

3

Short answer: Unnecessarily bad design.

Long answer: There are several things to consider.

First of all, a foreign key is first and foremost a constraint, a mechanism for ensuring data integrity/correctness. If the design precludes the use of them, you'll have to ensure integrity in other ways - using declarative multi-table constraints (if your DBMS supports them), transition constraints (ditto), triggers, application code, or nothing, in order of decreasing goodness. Foreign keys are simple, reliable and efficient. It's smart to make use of them.

Secondly, the design is confusing. I use ER modeling and diagrams like this primarily for sketching and documentation, not specification, since the notation is not rich enough to capture all the possibilities that the relational model offers, and that can limit my thinking. But this diagram doesn't work well in any case, because it does not clearly indicate how these tables work together. You need a substantial text box to explain, and the diagram in itself is pretty useless.

These drawbacks probably outweigh the questionable benefit of having fewer tables. (Tables are what the relational model is good at; no reason to be scared of them.) But of course, this must be evaluated in light of the full requirements and design of the database in question.

Jon Heggland
  • 274
  • 1
  • 7
1

The concern to have "fewer tables" just for the mere sake of it is usually completely misguided. In fact, when columns named "table_name" are included, that leaves no doubt that the designer was quite aware that he was actually combining multiple tables into one. In your case : text_values to be associated with the stuff in table1, text_values to be associated with the stuff in table2, and text_values to be associated with the stuff in table3.

Relationally speaking, it's indeed a complete antipattern but several "features" of the SQL language sometimes force designers into these patterns. (E.g. the lack of support for "distributed keys" - enforcing uniqueness of key values across [distinct-but-similar] tables.) Furthermore, the designer of that "shared_table" was probably thinking more of whatever UI would be needed to populate it, saw three tables that were "completely identical" (they aren't but for reasons he failed to see) and decided that it would be "better" to unify them so only one program/window/... would be needed for updating "shared_table". Waterbed theory. He saved some costs in terms of volume of code to be written ("duplicated" to his mind, no doubt) and new costs now arise elsewhere in the system in the form of more complex manipulation and integrity enforcement.

Rui
  • 3,454
  • 6
  • 37
  • 70
Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
0

This is easy to fix if you think about inheritance in ER modeling. Consider a base table with a PK that table1, table2 and table3 inherit from. That would make your FK1 point to an instance of that base table and make the entire thing work.

Alper
  • 3,424
  • 4
  • 39
  • 45
  • Thanks for your smart solution. But I also would like to hear some comments about this design, as personally I feel that is an abnormal design – Rui Jun 20 '18 at 12:19
  • As long as the three tables have something to do with each other and the union of their primary keys is unique, I don't see what would be weird about this. – Alper Jun 20 '18 at 12:29
  • You probably meant the one on base of your solution? What I meant is the original design, say in the original design there is no direct relationship between the 2 tables and the shared_table at all, isn't this abnormal? because in this way it is way difficult to make use of the ORM frameowork – Rui Jun 20 '18 at 13:02
  • Well, in the original design then you couldn't guarantee that your FK1 uniquely points to *one* table's PK, so that is a huge issue just by itself. – Alper Jun 20 '18 at 13:24
  • Do you mean there is no way to decide which table (tabl1 or table 2 or table2) the FK1 in `shared_table` points to without programmer's help? – Rui Jun 20 '18 at 13:30
  • No, I mean that there is no current way to guarantee that `table1.PK` and `table2.PK` are not both `1`. – Alper Jun 20 '18 at 13:44
  • You mean that based on the concept of foreign key, a foreign key refers to only one table, did I get your point now? – Rui Jun 20 '18 at 13:48
  • Yeah, a foreign key should point to something that is unique. Normally a primary key has a uniqueness constraint but you need some extra stuff to guarantee that uniqueness over multiple tables. – Alper Jun 20 '18 at 14:18