CODEWITHSUNDEEP
databasepostgresqlschema
tjar
tjar

Reputation: 299

best type for multiple values in a postgres db?

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

Upvotes: 1

Views: 198

Answers (2)

Kuberchaun
Kuberchaun

Reputation: 30362

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.

Upvotes: 1

Anomie
Anomie

Reputation: 94854

If each code corresponds to a single city, there are two possibilities:

  • The standard way to do it would be to have a city name table with a serial primary key, and then a table relating the alphanumeric code to the serial number (with an appropriate foreign key constraint, of course).
  • If nothing else is referring to the city names and there isn't a risk of user input error, you could also make a single table with a column for the alphanumeric code and the corresponding city.

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 serial primary 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.

Upvotes: 1

Related Questions