创建地址数据库 [英] creating a address database

问题描述

我重新创建了我公司数据库的一部分，因为它不符合未来的需求。
目前我们主要有一个平面文件和一些从未完全实现的分离表。

I am re-creating a part of my company’s database because it does not meet future needs. Currently we have mainly a flat file and some disjoined tables that were never fully realized.

我的思维方式是每个类别都有一个表，除了可能拉链表，可以作为一个连接在一起的表。
请参考下面的图片：

My way of thinking is we have a table for each category except maybe the zips table, which may serve as a connect it all together table. Please refer to image below:

数据库图http://www.freeimagehosting.net/uploads/248cc7e884.jpg

我想的一件事是删除zip表并且只需将zip代码放在zipstocities表中，因为邮政编码几乎是唯一的，然后在邮政编码上索引表。唯一的缺点是邮政编码必须是一个varchar来处理前导零的邮政编码。只是想知道我的逻辑是否有缺陷。

One thing I am thinking of is removing the zip table and just putting the zip code in the zipstocities table since the zip code is almost unique and then indexing the table on the zip code. The only downside is zip code has to be a varchar to take care of zip codes with leading zeros. Just want to know if there is a flaw in my logic.

推荐答案

我不知道美国的邮政编码和领土设计系统好的，但我认为它有点像德国人。

I don't know the US ZIPcode and territorial devision system well, but I assume it's somewhat like the German one.

• 一个州有很多县。


• 一个城市有很多城市。


• 一个城市有很多邮政编码。

所以我将使用以下模式。

Hence I would use the following schema.

ZipCodes          CityZipCodes
------------      ----------------      Cities
ZipCode (PK) <─── ZipCode (PK)(FK)      -----------
                  City    (PK)(FK) ───> CityId (PK)
                                        Name
                                        County (FK) ───┐
                                                       │
                                                       │
                                     Counties          │
                                     -------------     │
              States                 CountyId (PK) <───┘
              -----------------      Name               
              StateId      (PK) <─── State    (FK)
              Name
              Abbreviation

固定为每个邮政编码的多个城市。

Fixed for multiple cities per ZIP code.

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