邮政编码查找表 - 设计 Q
美国/加拿大邮政编码查找表已有 110 万行。我计划添加世界上所有国家的邮政编码。所以我假设表行会增长很多。所有这些数据将在现场用于查找（以提取城市/州/国家信息）+一些自动建议。
那么，是按国家/地区分解该表还是将其保留为世界邮政编码表并让它成长为一个巨大的查找表更好呢？
The US / Canada zip code lookup table has 1.1 million rows already. I am planning to add in all world countries zip codes. So I assume the table rows will grow a lot. All this data will be used on site for lookupts (to pull city/state/country info) + some auto suggest also.
So is it better to break the table down by country or leave this as a world zip code table and let it grow into a huge lookup table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
让它成长为一张大表，并根据需要优化/调整数据库。
除非在非常极端的情况下，数据库约束不应决定设计。
Let it grow into one big table and optimize/tune the DB as appropriate.
The database constraints should not dictate design except in the very extreme cases.
取决于您打算用它做什么。
我有一个位置数据库，有时获取纽约或加拿大的所有邮政编码很有用。
添加州和国家/地区字段可能是个好主意，即使您不使用它们。
只需确保您有针对邮政编码、国家/地区和州的单独索引即可。
Depends on what you plan to use it for.
I have a location database, and sometimes it is useful to get all the zip codes in new york for example, or just Canada.
Adding a field for state and country is probably a good idea, even if you don't use them.
Just make sure you have separate indexes for zip, country, and state.
使用单个整体表的一个问题是，不同国家/地区在不同地点使用一些邮政编码（不，我不知道有什么例子）。
根据您的表的设置方式，这可能会或可能不会成为问题。希望您的桌子没有在邮政编码上被 PKd，否则您会遇到麻烦。
您肯定需要至少添加一个国家/地区字段，并且可能还需要添加一个州/省/地区/等字段，以便消除此类情况的歧义。
One issue with using a single, monolithic table is that there are some postcodes that are used by different countries for different places (no, I don't know of an example off the top of my head).
Depending how your table is set up, that may or may not be a problem. Hopefully your table isn't PKd on the postcode, or you'll be in trouble.
You'll definitely need to add at least a country field, and probably also a state/province/prefecture/etc field too in order to disambiguate such occurrences.