哪个数据库设计有更好的表现？ [英] Which database design gives better performance?

问题描述

我想要选择以检索个人，并进一步进行插入，删除和更新。

如果我想要获取个人谁住在巴西将会是什么最好的方法？

制作2个外键 city 和 country 表人：

 个人（id，名称，职业，** id_country **，** id_city **）
 cities（id，city，** id_country **）
国家（id，国家）
  

或表个人城市 c $ c>和另一个外键县在表城市

$ b $个人（id，name，profession，** id_city **）
cities（id，city，** id_country **）
countries（id ，国家）

或使视图像第一个选项？ p>

为了插入，删除和更新数据，它们还是最好的表吗？性能没有差异？我也感到困惑，什么可以影响架构的性能？

解决方案

这个问题与你制作yestarday的问题非常相似：

创建许多表格或只创建一个

答案也是相似的 - 这取决于你想要实现什么。这两种解决方案都可以工作，既有利弊，也可以根据具体情况进行一些小小的权衡分析。在这种情况下，不可能回答你的问题。

我在两个版本中看到的唯一区别是Person表中的外键id_country：

个人（id，name，professional， ** id_country ** ，id_city）

cities（id，city，id_country）

国家（id，国家）

问题是我们需要吗？

，两种解决方案的利弊：

1。解决方案：使用id_contry：

• 优点：轻松地根据土地（更简单的查询）查询


• cons：更复杂的底层数据库和更多的冗余，更有可能导致DB中的不一致，更难更新

2。解决方案：没有id_country：

• 优点：更简单，更干净的模型，没有冗余，更容易维护


• cons：较慢的性能和更复杂的查询，用于根据土地（简单查询）重新检索一个人

所以，第一个解决方案有效地为您提供了更简单的查询结构和更好的性能来检索国家（你想要的），但它有其成本（参见利弊）。另一方面，务实的思考认为，国家城市的数据是非常稳定的，而不是经常发生变化，这个事实有利于第一个解决方案。

如果这种非规范化和轻微的不一致的可能性您可以生活在一起，您可以采取第一个解决方案。

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?

解决方案

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.

这篇关于哪个数据库设计有更好的表现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！