数据库规范化混乱? [英] Database Normalization confusion?

问题描述

我有 5 张桌子:

1. tbl省
2. tblDivision
3. tblDistrict
4. tblCity
5. tbl 选区

tblProvince:

• 身份证
• 姓名
• 面积

TblDivision:

• 身份证
• 姓名
• 面积
• Province_id

tblDistrict:

• 身份证
• 姓名
• 面积
• Province_id
• 部门_id

tblCity:

• 身份证
• 姓名
• 面积
• Province_id
• 部门_id
• District_id

TblConstituency:

• 身份证
• 姓名
• 面积
• Province_id
• 部门_id
• District_id
• City_id

这是存储数据的正确方式吗?添加该区域的完整细节.

Is this the right way to store data? adding complete detail of the Area.

或者我应该只保存前一个区域的 id(比如 city 是选区的容器).要存储选区的详细信息，我应该用它来广告 city_id.

Or should I just save the id of the previous area (like city is the container of constituency). to store detail of constituency i should ad city_id with it.

像这样...

tblCity
+-------------+
| ID          |
+-------------+
| Name        |
+-------------+
| Area        |
+-------------+
| District_id |
+-------------+

TblConstituency
+-------------+
| ID          |
+-------------+
| Name        |
+-------------+
| Area        |
+-------------+
| City_id     |
+-------------+

如果我想获得有关选区的完整详细信息，我可以得到城市的 id，从城市我可以得到区，从区我可以得到分区等等.

and if i want to get complete detail about constituency i could get id of city, and from city i could get district, and from district i could get division and so on.

但是，我觉得这是一个巨大的矫枉过正.每次我需要获取详细信息时，这都会是一个很长的查询.

But, I feel its a massive overkill. it will be a long query every time i need to get detail.

那么，最好的方法是什么?

So, what's the best way?

P.S:对于描述我的问题的糟糕方式深表歉意.

P.S: apologies for poor way of describing my question.

推荐答案

永远不要将相同的信息存储两次.这意味着您必须手动保持该信息同步，而同步既困难又容易出错.基本上，任何时候你有多个事实来源，你就有零个事实来源.

Never store the same information twice. This means you would have to manually keep that information synchronized, and synchronization is difficult and error-prone. Basically, any time you have multiple sources of truth, you have zero sources of truth.

在这里考虑您的桌子:

Division
+-------------+
| ID          |
+-------------+
| Name        |
+-------------+
| Area        |
+-------------+
| Province_id |
+-------------+



District
+-------------+
| ID          |
+-------------+
| Name        |
+-------------+
| Area        |
+-------------+
| Province_id |
+-------------+
| Division_id |
+-------------+

Division 已经在存储 Province_id.那么为什么 District 也需要存储它呢?如果District 存储的不同 Province_id 与其对应的Division 记录相比会发生什么?哪个对于那个District 是正确的?

Division is already storing the Province_id. So why does District need to store it as well? What happens if District stores a different Province_id than its corresponding Division record? Which one is correct for that District?

只需链接到直接父记录:

Just link to the direct parent record:

District
+-------------+
| ID          |
+-------------+
| Name        |
+-------------+
| Area        |
+-------------+
| Division_id |
+-------------+

与Division表相关的信息已经存在，可以查询.(基本上，这就是 JOIN 关键字的用途.)既然您已经掌握了信息，则无需重复.

The information, by relation to the Division table, already exists and can be queried. (Basically, that's what the JOIN keyword is for.) Since you already have the information, you don't need to repeat it.

这篇关于数据库规范化混乱?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！