CODEWITHSUNDEEP
sqlmodelrelational-database
Hernan Demczuk
Hernan Demczuk

Reputation: 405

Foreign key vs intermediate tables

This is a very basic question and I apologize for that, but I didn't find any resource to answer this question.

I see these diagrams everywhere and I am having a hard time trying to understand why they use an intermediate table to relate State and Country instead of just adding a foreign key in State table pointing to Country table.

Is it because some RDMS don't allow foreign keys? Is it because the name of the state can be in many countries?

enter image description here

Source: Database Design and Naming Conventions

Upvotes: 3

Views: 2786

Answers (2)

duffymo
duffymo

Reputation: 308753

This design makes no sense to me.

The intermediate tables are used for many-to-many relationships. For example, an academic database might have Student and Course tables, with a Student_Course (or Transcript) in between. A Student can take many Courses, and a Course can have many Students.

It this case a State can only belong to one Country, so the design seems silly.

A foreign key is the way to express a one-to-many relationship, with the key added to the many child to point to its single parent.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

This type of table is called a junction table and is used to represent many-to-many relationships. As far as I know, all states are within a single country, so there is no need to have a junction table. A state could simple have a country_id column. You are correct.

You might think the same of the "county" and "city" entities. At least in the United States, this is not strictly true (unless you follow the Census Bureau definitions). Some cities do span multiple counties.

As a write this, I am in New York County (more commonly known as "Manhattan"), which is part of New York City. New York City actually comprises five counties. Other cities also span counties, such as Chicago (Cook and DuPage counties), Atlanta (Fulton and Dekalb), and many other (listed on this Wikipedia page). New York City of course "wins", spanning five, more than any other ;)

Upvotes: 4

Related Questions