Youssef Youssef - 1 year ago 59
SQL Question

Which database design gives better performance?

I want to

to retrieve
and also further make some inserts, deletes and updates.

If I want retrieve
who lives in
what will be the best approach?

Make 2 foreign key
in table

Person(id, name, profession, **id_country**, **id_city**)
cities (id, city, **id_country**)
countries (id, country)

or just one foreign key of
in table
and a other foreign key
in table

Person(id, name, profession, **id_city**)
cities (id, city, **id_country**)
countries (id, country)

or make a view like the first option?

For inserting, deleting and updating data, are they still the best tables? No difference in performance ? Also I'm confused, what can affect performance in a schema?

Answer Source

This question is very similar to the one you made yestarday:

Create many tables or just one

The answer is also similar - that depends on what you want to achieve. Both solutions could work and both have pros and cons and one should do a little trade-off analysis in the light of the specific situation. Out of this context is not possible to answer your question.

The only difference I see in both version is foreign key id_country in Person table:

Person(id, name, profession, **id_country**, id_city)
cities (id, city, id_country)
countries (id, country)

The question is "do we need it?"

So, the pros and cons of both solutions:

1. Solution: With id_contry:

  • pros: easier retrival of a Person based on land (simpler query) and better performance of this query
  • cons: more complex underlaying DB and more redundancy, more chance of inreoducing inconsiscenties in the DB, harder updates

2. Solution: Without id_country:

  • pros: simpler and cleaner model, no redundancy, easier maintenance
  • cons: slower performance and more complex query for retrival of a Person based on land (simpler query)

So, the 1st solution effectivelly gives you easier query structure and better performance for retrieving Persons by Country (what you wanted), but it has its cost (see pros and cons). On the other side, pragmatic thinking says that country-city data are quite stabile and not often changed and this fact goes in favor of the 1st solution.

If this denormalization and slight chance of inconsistencies something you can live with, you can take the 1st solution.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download