a question in tables relations

Question

• Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys
• My questions:
  • Should i link Table A to Table C too?? Or Table B can act as intermediate link between both??
  • If i linked between Table A to Table C will that affect on DB perfomance and data quiring positively or negatively??

Answer 1

Plan 1

If tables COUNTRY, CITY, and 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 COUNTRY.

Plan 2

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 STREET with COUNTRY.

Plan 3

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.

Don't. The 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 COUNTRY and STREET directly.

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:

Plan 1b

Keep plan1 design (primary and foreign keys) but define the (plan2's) single-column keys as the clustered indexes.