如何在数据库中存储地址 [英] How to store address in database

问题描述

我正在开发一个CRM应用程序，该应用程序存储与我们合作的公司有关的一些数据。例如：CEO的名称和总部的实际地址。我经常需要查找位于特定城市的公司，然后按街道名称对搜索结果进行排序。

I am developing a CRM application that stores some data on companies we work with. Such as for example: CEO name and physical address of their head office. I will often need to look up for companies located in particular city and then sort the search result by street name.

我知道正确的解决方案很可能类似于整数类型的address列，它将指向地址表，该地址表本身将包含其他列，例如州，城市，街道，housing_number，office_number，所有这些列本身要么是指向相关表（城市，州，街道的整数） ）或最后一块数据（housing_number，office_number）。

I know that proper solution will most likely be something like an address column of the integer type, which will point to the address table, which itself will contain other columns like state, city, street, housing_number, office_number all of which will itself be either an integers pointing to related tables (city, state, street) or the final piece of data (housing_number, office_number).

问题不仅仅在于我更愿意处理一两个链接的事实表格，而不是在3、4（像我之前假设的设计）或更多表格之间使用困难的JOIN，但是我真的不明白不做这样的事情的意思：

The problem is not just the fact that I'm much more comfortable working with something like one or two linked tables, rather than using difficult JOINs across 3, 4 (like the design i supposed earlier) or even more tables, but also that i don't really see the point of not doing something like this:

| id  |      name       |  ceo_name   |    city     |     street       | house  | office |
|-----|-----------------|-------------|-------------|------------------|--------|--------|
|  1  | Company Name 1  | CEO Name 1  | New-York    | 5th Ave          |    22  |     12 |
|  2  | Company Name 2  | CEO Name 2  | New-York    | 44th St.         |    42  |     88 |
|  3  | Company Name 3  | CEO Name 3  | Boston      | Irish Lane       |     2  |     14 |
|  4  | Company Name 4  | CEO Name 4  | Washington  | Tahoe boulevard  |    54  |     19 |

如果实施这样的解决方案，我会遇到什么问题？我拥有所有的原子，因此如果需要增加，我以后可以随时实施3-NF解决方案。

What problem can i ran into if i implement such a solution? I have all atomics so if the need rises i can always implement the 3-NF solution later on.

推荐答案

类似的db应用程序的实际模式，可能用作样板：

Actual schema for the similar db application, maybe used as a boilerplate:

这篇关于如何在数据库中存储地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！