I have a table of cites and zip codes. As it happens one city can have multiple zip codes so how should i set the db that i may be able to pull the correct zips when i enter a city name or get the correct city when i enter the zip.
我有一张引用和邮政编码表。因为它发生了一个城市可以有多个邮政编码所以我应该如何设置数据库，当我输入城市名称或在我输入拉链时获得正确的城市时，我可能能够拉出正确的拉链。
Should this be one table with all the data, or three where one has cities, one has zips and third has the proper associations?
这应该是一个包含所有数据的表，还是三个有城市的表，一个有拉链，第三个有正确的关联？
2 个解决方案
#1
3
I would use only one table with zip codes as primary key and an index on the cities (to speed up searching). The three-table solution may me useful if you have a many-to-many relationship (one zip code can have multiple cities), but otherwise I think it's only unnecessary complexity.
我只使用一个带有邮政编码的表作为主键，并使用城市的索引（以加快搜索速度）。如果您有多对多关系（一个邮政编码可以有多个城市），那么三表解决方案可能会很有用，但我认为这只是不必要的复杂性。
#2
0
Having lived in Kissimmee and worked for UPS (memorizing zip codes), the cities of Kissimee and Celebration in Florida have the same zip code 34747. I personally use multiple tables: countries, regions (states), and localities (cities). The countries table has country name and other relevant data. The regions table is basically the same but has a foreign key to the country name. The localities table has foreign key to the region name, plus the postal code and other relavant data. I create a primary key referencing multiple columns in the localities table (name, region, postal_code), regions table (name, country), and countries table (name) to help with performance. My localities table can therefore handle (Kissimmee, FL, 34747) and (Celebration, FL, 34747) as well as (Orlando, FL, 32801) and (Orlando, FL, 32802). In any case, I handle returning multiple results if I search by zip code or city. However, depending on how your source data is a acquired, you may want to make a Boolean "metropolis" column in your localties table to indicate (in my example) that Kissimmee is the metrolopitan area for Celebration and only allow one metropolis per zip code.
住在基西米并为UPS工作（记忆邮政编码），基西米和佛罗里达州庆典的城市有相同的邮政编码34747.我个人使用多个表：国家，地区（州）和地方（城市）。国家/地区表有国名和其他相关数据。区域表基本相同，但具有国家/地区名称的外键。 localities表具有区域名称的外键，以及邮政编码和其他相关数据。我创建了一个主键，引用了localities表（name，region，postal_code），regions表（name，country）和countries表（name）中的多个列来帮助提高性能。因此，我的地方表可以处理（Kissimmee，FL，34747）和（Celebration，FL，34747）以及（Orlando，FL，32801）和（Orlando，FL，32802）。在任何情况下，如果我通过邮政编码或城市搜索，我会处理多个结果。但是，根据获取源数据的方式，您可能需要在localties表中创建一个布尔“metropolis”列，以指示（在我的示例中）Kissimmee是庆典的metrolopitan区域，并且每个邮政编码只允许一个大都市。
#1
3
I would use only one table with zip codes as primary key and an index on the cities (to speed up searching). The three-table solution may me useful if you have a many-to-many relationship (one zip code can have multiple cities), but otherwise I think it's only unnecessary complexity.
我只使用一个带有邮政编码的表作为主键，并使用城市的索引（以加快搜索速度）。如果您有多对多关系（一个邮政编码可以有多个城市），那么三表解决方案可能会很有用，但我认为这只是不必要的复杂性。
#2
0
Having lived in Kissimmee and worked for UPS (memorizing zip codes), the cities of Kissimee and Celebration in Florida have the same zip code 34747. I personally use multiple tables: countries, regions (states), and localities (cities). The countries table has country name and other relevant data. The regions table is basically the same but has a foreign key to the country name. The localities table has foreign key to the region name, plus the postal code and other relavant data. I create a primary key referencing multiple columns in the localities table (name, region, postal_code), regions table (name, country), and countries table (name) to help with performance. My localities table can therefore handle (Kissimmee, FL, 34747) and (Celebration, FL, 34747) as well as (Orlando, FL, 32801) and (Orlando, FL, 32802). In any case, I handle returning multiple results if I search by zip code or city. However, depending on how your source data is a acquired, you may want to make a Boolean "metropolis" column in your localties table to indicate (in my example) that Kissimmee is the metrolopitan area for Celebration and only allow one metropolis per zip code.
住在基西米并为UPS工作（记忆邮政编码），基西米和佛罗里达州庆典的城市有相同的邮政编码34747.我个人使用多个表：国家，地区（州）和地方（城市）。国家/地区表有国名和其他相关数据。区域表基本相同，但具有国家/地区名称的外键。 localities表具有区域名称的外键，以及邮政编码和其他相关数据。我创建了一个主键，引用了localities表（name，region，postal_code），regions表（name，country）和countries表（name）中的多个列来帮助提高性能。因此，我的地方表可以处理（Kissimmee，FL，34747）和（Celebration，FL，34747）以及（Orlando，FL，32801）和（Orlando，FL，32802）。在任何情况下，如果我通过邮政编码或城市搜索，我会处理多个结果。但是，根据获取源数据的方式，您可能需要在localties表中创建一个布尔“metropolis”列，以指示（在我的示例中）Kissimmee是庆典的metrolopitan区域，并且每个邮政编码只允许一个大都市。