Which database design gives the best performance?

Question

I want to selectextract personas well as make some insertions, delete and update.

If I want to get personwho lives in Brazil, what would be the best approach?

Make 2 foreign keys cityand countryin the table person:

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

or only one foreign key citiesin the table personand another foreign key countyin the tablecities

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

or make a presentation how is the first option?

To insert, delete and update data, are they the best tables? The difference in performance? Also I am confused, what could affect the performance in the circuit?

Answer 1

, yestarday:

- , . , , . .

, , - id_country Person:

(id, , , **** id_country ****, id_city)
(id, city, id_country)
(id, )

: " ?"

, :

1. : id_contry:

• : Person ( )
• : , , .

2. : id_country:

• : , ,
• : Person ( )

, effectivelly ( ), (. ). , , , 1- .

, , 1- .

Answer 2

, , denormalize ¹ . , (, ).

, () .

, , : " " :

cluster . , , COUNTRY_ID, , - ².

, CITY_NO PERSON_NO, PERSON - , .. , "" " , / , , .

---

¹ . () , , .

² I/O .

Answer 3

, . . . " ":

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

. " ":

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

, . Star , , . .

, .

Answer 4

( , , "", , , .)

- . . . ( .) . - , 5NF , . - , . .

- , , , . (, , .) , , , , , . ( , .)

, , . , - .

, ( ), , , , . , , . , , , . . , , - , .

person(id, name, profession, city, country)
    -- person [id] is named [name] and practises [profession] in [city], [country]  
city (name, country) -- [name] uniquely names a city within country [country]
country (name) -- [name] uniquely names a country

FK, , . 5NF.

You can understand that the next design (you can add the appropriate restrictions) may be better for you than the previous one, which will have nothing to do with performance. Then you can go to it and offer the previous tables in the form of views to old users, which will have nothing to do with performance.

person(id, name, profession, id_city)
    -- person [id] is named [name] and practises [profession] in [id_city]  
city (id, name, id_country) -- city [id] is named [name] and is in country [id_country]
country (id, name) -- country [id] is named [name]

Here id_country will personally violate 5NF, since it will be functionally dependent on the non-key id_city.