如何优雅地设计代表ADDRESS的数据库模式？

by address i only mean normal address like country,state,city,district, street,building,

按地址我只是指普通地址，如国家，州，城市，地区，街道，建筑物，

where the address can be efficiently referenced in other tables like people, so that we can select people in the same city or so on? thx.

地址可以在人们等其他表格中有效地引用，以便我们可以选择同一城市的人员等等？谢谢。

3 个解决方案

#1

Well depend on how you want to normalize the db, (warehouse or transactions)

很好地取决于你想如何规范化数据库，（仓库或交易）

Example 1: unnormalized, everything in one table

示例1：非标准化，一个表中的所有内容

table name: user

表名：用户

attribute: userid, username, country

属性：userid，username，country

sql to retrieve:

要检索的SQL：

 select username from user where country="USA"

Example 2: normalized, everything in separate table

示例2：规范化，所有内容都在单独的表中

table name: user attribute: userid, user name, countryID

表名：用户属性：userid，用户名，countryID

table name: country attribute: countryID, country name

表名：国家/地区属性：countryID，国家/地区名称

sql to retrieve:

要检索的SQL：

 select username from user inner join country where country="USA"

You need to know what the db is used for to determine the "efficient" way.

您需要知道db用于确定“高效”方式的内容。

#2

In my experience, you need country, state, city, zip, address.

根据我的经验，您需要国家，州，城市，邮编，地址。

Only the first three/four are convenient to filter users. Enum fields are very suitable for the first two. The next two are ideally validated using APIs -- this will spare you the hassle of needing to maintain a list of valid values.

只有前三个/四个方便过滤用户。 Enum字段非常适合前两个字段。接下来的两个使用API​​进行了理想的验证 - 这将使您无需维护有效值列表。

I've yet to run into any system (though I assume a post office would need it, along with geolocation) that needs the address part to be chunked in individual pieces of data for more precise filtering -- plus, each user has his own way of entering the latter.

我还没有遇到任何系统（虽然我认为邮局需要它，以及地理位置）需要将地址部分分成单个数据块以进行更精确的过滤 - 此外，每个用户都有自己的系统进入后者的方式。

Keep in mind that some countries have no state; that others have no zip codes; and that zip code formats vary widely from a country to the next.

请记住，有些国家没有国家;其他人没有邮政编码;而且邮政编码格式因国家而异。

Also keep in mind that, even when a user can have multiple addresses in your system, the last thing you want is to tie multiple users to the same address_id. They're usually better placed as details of the users (or their company), or as 1-n related details towards the latter; never n-n. When not, UI issues quickly creep in, and someone will invariably edit the address of user B by mistake, because the latter happens to share it with user A.

另外请记住，即使用户可以在系统中拥有多个地址，您最不希望的是将多个用户绑定到同一个address_id。它们通常更好地作为用户（或其公司）的详细信息，或作为后者的1-n相关细节;从不n-n。如果没有，UI问题很快就会蔓延，并且有人会错误地编辑用户B的地址，因为后者碰巧与用户A共享它。

#3

Here is one extended database structure for address representation,
Advantages with this approach
1. You can add city, country, state later on.
2. it supports edting of city country or state.
3. City is mapped to State and similarely state is mapped to Country. So you will just be storing city in an addrss. You are not needed to store state and country in each address, thus reducing redundancy.
4. You can generate a list of State whenever user chooses a country. Similarely you can generate a list of city when user chooses a state.

这是一个用于地址表示的扩展数据库结构，这种方法的优点1.您可以稍后添加城市，国家/地区。 2.它支持城市国家或州的编辑。 3.城市映射到州，同类州映射到国家。因此，您只需将城市存储在地址中即可。您不需要在每个地址中存储州和国家，从而减少冗余。 4.只要用户选择国家/地区，您就可以生成状态列表。同样，您可以在用户选择州时生成城市列表。

Address
   id            INT        PK    AUTO_INCREMENT
   street        VARCHAR    
   city_fk       INT        FK    
   Zip_code      VARCHAR
City
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR
   state_fk      INT        FK
State
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR
   country_fk    INT        Fk
Country
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR

user
   id            INT        PK    AUTO_INCREMENT
   # other details
user_address_mapping                              # So that user can have multiple address
   id            INT        PK    AUTO_INCREMENT
   user_fk       INT        FK                    # Link to user
   address_fk    INT        FK                    # Foreign key to address

EDIT: (Thanks to @Denis comment)
Or If your contry does not have states (Or you want a generic solution) here is the structure.

编辑:(感谢@Denis评论）或者如果你的contry没有状态（或者你想要一个通用的解决方案），这里是结构。

Address
   id            INT        PK    AUTO_INCREMENT
   street        VARCHAR    
   city_fk       INT        FK    
   state_fk      INT        FK
   country_fk    INT        FK
   Zip_code      VARCHAR
City
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR
State
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR
Country
   id            INT        PK    AUTO_INCREMENT
   name          VARCHAR

user
   id            INT        PK    AUTO_INCREMENT
   # other details
user_address_mapping                              # So that user can have multiple address
   id            INT        PK    AUTO_INCREMENT
   user_fk       INT        FK                    # Link to user
   address_fk    INT        FK                    # Foreign key to address
   # Here user_fk & address_fk should be composite unique key, so that users can not share an address.