-2

When I make ER diagram in MySQL Workbench the software makes automaticly multiple foreign key columns where I would have expected just one foreign key:

enter image description here

In the image you see the following foreign keys with multiple columns:

  • fk_city_state1 in table city: state_state_id + state_country_country_id
  • fk_district_city1 in table district: city_city_id + city_state_id + city_state_country_id
  • fk_street_district1 in table street: district_district_id + district_city_city_id + district_city_state_id + district_city_state_country_country_id

From this post I learned that Workbench does this for index purposes.

When I make such an ER diagram in pgAdmin 4 these extra foreign key columns aren't added. Is this about how advanced is the pgAdmin 4 tooling or isn't it a good practise in PostgreSQL? With other words: is it a good idea to add these extra columns as foreign keys and put an Auto FK Index on them?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
DevQandA
  • 51
  • 6
  • You are comparing two different tools for two different databases. There are dozens (if to hundreds or even thousands) of other tools for these databases, and every tool has its own habits. Creating foreign keys is not something specific to MySQL or PostgreSQL, it's something you do in a relational model. Same thing with indexes, are they useful or not? And a foreign key is for data integrity, not for speeding up finding data. – Frank Heikens May 11 '23 at 11:19
  • Your question doesn't make any sense though. Foreign-keys are a matter of relational design and nothing to do with your RDBMS. Indexes are a matter of performance and nothing to do with your relational design. – Richard Huxton May 11 '23 at 12:31
  • I do know that. As mentioned I expected in 4 out of 5 tables only one foreign key. Workbench adds more. In another SO post which I included it is mentioned that Workbench does that for indexing purposes. That is correct, in Workbench I see the index automaticly made. I don't care MySQL or Workbench though. I mentioned it how I came to my question. What is the benifit of having 4 FK in table street instead of just one on which I can add an index? And if this is benifical, would it be also benificial in PostgreSQL? – DevQandA May 11 '23 at 12:41
  • 1
    I have never seen a tool that does magically create some additional foreign keys. If it's beneficiary to PostgreSQL or any other database? I don't think so, but it's something you can only measure when you sufficient data in these tables. The same thing for indexes, you first need data to prove it. For example an index on country will most likely be useless, there are just 200 countries in the world. You have to measure it. – Frank Heikens May 11 '23 at 14:23
  • 1
    Whether or not those columns and indexes are created is a design decision based on speed, space, and other considerations. From an information standpoint they are redundant. For example, given a street, the district can be directly determined and from the district the city can be determined. The associations can be followed all the way back to country. The extra columns and indexes take more space and adversely affect insert performance but might speed data retrieval. – JohnH May 11 '23 at 18:54
  • What is your 1 (specific researched non-duplicate) question? It's not clear what you are trying to ask. "the software automatically makes multiple foreign key columns"--What exactly did you input to what (configured how) & when in that process did which columns/FKs arise? [mre] "Is this about"--What is "this"? You say "In other words", but the 2 sentences don't say the same thing. Etc. PS Please put all & only what you need to ask your question in your post. Quote with credit & relate to your question. (Don't expect us to read an entire page & guess what part(s) are relevant & why.) – philipxy May 11 '23 at 21:00
  • Please clarify via edits, not comments. Please delete & flag obsolete comments. ) An ERD is an image of DDL. [Why should I not upload images of code/data/errors?](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Give just what you need & relate it to your problem. – philipxy May 11 '23 at 21:03

1 Answers1

0

Based on the comments I found my answer. From an information standpoint the extra FK columns are redundant. For example, given a street, the district can be directly determined and from the district the city can be determined.

The extra columns and indexes take more space and adversely affect insert performance but might speed data retrieval, which should be tested.

DevQandA
  • 51
  • 6