STREET were in identifying relationship, then ER diagram would be like below:
With identifying relationship, table
STREET already has column
country_id, so it has no difficulty to directly join with table
But if (and as I think is the case) the tables have non-identifying relationship, then ER diagram will be like below:
With this, we cannot join table
STREET with table
COUNTRY directly because there is no key to connect. So we have to use table
CITY in order to join
Sometimes we want to avoid this (usually performance issues), so add an additional relationship from table
STREET to table
COUNTRY. This is called Denormalization. We often do this for performance perspective. Surely, Joining with two tables has better performance than with three tables. But denormalization always bring about many difficulties about maintaining consistency.
In short, we assume that you now have plan2 and you are considering to move to plan3.
STREET -> COUNTRY foreign key constraint is redundant.
You can though choose plan1 if you want. You still have only two foreign key constraints (no redundancy) and you can join
If you are not fond of the compound (multi-column) primary keys or you are afraid of this causing performance issues (in comparison with simple
IDENTITY primary keys), you could use a variation on plan1:
Keep plan1 design (primary and foreign keys) but define the (plan2's) single-column keys as the clustered indexes.