postgres 数据库中多个值的最佳类型？
嗨，大家好 我有一个 postgres 数据库，其中包含数千个城市。我想为每个城市分配未指定数量的字母数字代码（每个城市最多 1000 个）。最有效的方式是什么？谢谢
Hi guys
I have a postgres db with thousands of cities in it. I want to assign an unspecified number of alphanumeric codes to each (as many as 1000 per city). What would be the most efficient manner in which to do this? Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果每个代码对应一个城市，则有两种可能性：
serial主键的城市名称表，然后是一个将字母数字代码与序列号（当然有适当的外键约束）。如果一个代码可以映射到多个城市，标准方法是拥有一个城市名称表和一个字母数字代码表，每个表都有一个
serial主键，然后使用第三个表来映射来自一个城市的主键表到另一个表中的主键（当然，两个表都有外键）。如果字母数字代码足够短，您可以将它们编码为 int 或 bigint（即 4 到 8 字节），您可以使用该编码来存储它们，而不是使用单独的映射表。If each code corresponds to a single city, there are two possibilities:
serialprimary key, and then a table relating the alphanumeric code to the serial number (with an appropriate foreign key constraint, of course).If a code can map to multiple cities, the standard method would be to have a city name table and an alphanumeric code table each with a
serialprimary keys, and then a third table mapping the primary key from one table to the primary key in the other (with foreign keys to both, of course). If the alphanumeric codes are short enough that you could encode them in an int or bigint (i.e. 4 to 8 bytes), you could possibly use that coding to store them instead of having a separate mapping table.根据您的问题，您可以创建一个城市表（听起来您已经拥有该表）并创建一个代码表并创建一个 city_code 表。在 city_code 表上创建一个返回 city 的外键，并在 city_code 表上创建一个从 city_code 到 code 表的外键。确保外键上有索引以提高性能。
至于数据类型，您可以在代码表列上创建适合您的数据类型。如果您不知道可以使用 TEXT 的长度，并且如果您在某些时候需要将其限制为固定大小，则可以在 TEXT 列上放置检查约束。对于你所要求的事情，最重要的是标准化。这是通过创建 city_code 和代码表来完成的。不要无正当理由地反规范化。在大多数情况下，往往很难找到有效的理由。
Based on your question you can just create a city table, which you already have by the sounds of it and create a code table and create a city_code table. Create a foreign key back to city on the city_code table and a foreign key from city_code to the code table. Make sure you have a index on the foreign keys for performance.
As for datatype you can create what ever works for you on the code table column. If you don't know the length you can use TEXT and if you at some point need to constrain it to a fixed sized you you can put a check constraint on the TEXT column. The important thing for you to do for what you ask is normalize. This is accomplished by creating the city_code and code table. Don't de-normalize for no valid reason. And a valid reason is often hard to come by in most cases.