Youssef Youssef - 8 days ago 5
SQL Question

Which database design gives better performance?

I want to

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

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

Make 2 foreign key
city
and
country
in table
person
:

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


or just one foreign key of
cities
in table
person
and a other foreign key
county
in table
cities


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

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.