Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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??

an example

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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 1

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 2

Plan 3

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.

share|improve this answer
    
I'm also rookie in English. If you find any awkward expressions or mistakes, please feel free to edit. It really helps me. Thank you. –  kangbu Nov 7 '12 at 5:08
    
Thank you, ypercube, also for correcting. I uploaded my images. –  kangbu Nov 7 '12 at 7:00
    
so ur own opinion is to link country and street tables together to increase performance though what i will see from difficulties about maintaining consistency?? OR avoid linking between them together and keep my DB with lower performance to avoid difficulties about maintaining consistency??? –  Hatem Ghazy Nov 7 '12 at 9:41
2  
I suggest you that avoid a denormalization as possible as you can (It means do not link between COUNTRY and STREET tables in both cases.) It's natural and right for data modeling. What I said-joining two tables is better than three table in performance perspective- is right, but have a very little difference. –  kangbu Nov 7 '12 at 12:05
1  
In this case, each table's record count will be like this: COUNTRY < CITY < STREET (e.g. 100 < 1000 < 100000, respectively). We usually want to retrive some records from STREET table mainly, and want to know what COUNTRY is for each STREET. So, performance issues at CITY or COUNTRY table are less than at STREET table. But really some cases (e.g. CITY table has extremly big /or want to pull up a performance by millisecond), they do denormalization,.. only for specific reason. Denormalization is not recommended in most cases. –  kangbu Nov 7 '12 at 12:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.