This is the database schema (in Postgreql syntax) that I use for postal codes:
CREATE TABLE "country" (
"country_pk" serial NOT NULL,
"country" character varying(64) NOT NULL,
Constraint "country_pkey" Primary Key ("country_pk")
);
CREATE TABLE "province" (
"province_pk" serial NOT NULL,
"country_pk" integer,
"state_or_province" character(2) NOT NULL,
"state_or_province_name" character varying(45),
Constraint "province_pkey" Primary Key ("province_pk")
);
CREATE TABLE "city" (
"city_pk" serial NOT NULL,
"province_pk" integer,
"city" character varying(45),
Constraint "city_pkey" Primary Key ("city_pk")
);
CREATE TABLE "postal_code" (
"postal_code_pk" serial NOT NULL,
"city_pk" integer NOT NULL,
"postal_code" character varying(10) NOT NULL,
Constraint "postal_code_pkey" Primary Key ("postal_code_pk")
);
Note that:
1) I've inadvertently omitted the foreign key constraints, but they should be there, matching to obvious column names. Something like
on province: constraint province_fkey foreign key country_pk references country (country_pk)
on city: constraint city_fkey foreign key province_pk references province(province_pk)
on postal_code: constraint postal_code_fkey foreign key city_pk references city(city_pk)
2) postal_codes to city has to support a many-to-many relationship since a town, usually a large city, can have more than one zip code and a single zip code, for usually a group of small towns, can be served by a single zip code.
3) I don't use the zip code as a primary key...the USPS, being a government agency, can and probably will whimsically change zip code assignments from time-to-time.
4) I don't have the lat/long coordinates, but I'm thinking about adding them from your database to mine. And also, because of #3, I would recommend AGAINST having the latitude and longitude in the postal_code table: it rightly belongs in the city table, since the city won't move (and the zip code could possibly be reassigned elsewhere).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
How's it coming? were you guys going to create the code and sql to make this useful?
Thanks.
What do you want the code to do?
This is the database schema (in Postgreql syntax) that I use for postal codes:
CREATE TABLE "country" (
"country_pk" serial NOT NULL,
"country" character varying(64) NOT NULL,
Constraint "country_pkey" Primary Key ("country_pk")
);
CREATE TABLE "province" (
"province_pk" serial NOT NULL,
"country_pk" integer,
"state_or_province" character(2) NOT NULL,
"state_or_province_name" character varying(45),
Constraint "province_pkey" Primary Key ("province_pk")
);
CREATE TABLE "city" (
"city_pk" serial NOT NULL,
"province_pk" integer,
"city" character varying(45),
Constraint "city_pkey" Primary Key ("city_pk")
);
CREATE TABLE "postal_code" (
"postal_code_pk" serial NOT NULL,
"city_pk" integer NOT NULL,
"postal_code" character varying(10) NOT NULL,
Constraint "postal_code_pkey" Primary Key ("postal_code_pk")
);
Note that:
1) I've inadvertently omitted the foreign key constraints, but they should be there, matching to obvious column names. Something like
on province: constraint province_fkey foreign key country_pk references country (country_pk)
on city: constraint city_fkey foreign key province_pk references province(province_pk)
on postal_code: constraint postal_code_fkey foreign key city_pk references city(city_pk)
2) postal_codes to city has to support a many-to-many relationship since a town, usually a large city, can have more than one zip code and a single zip code, for usually a group of small towns, can be served by a single zip code.
3) I don't use the zip code as a primary key...the USPS, being a government agency, can and probably will whimsically change zip code assignments from time-to-time.
4) I don't have the lat/long coordinates, but I'm thinking about adding them from your database to mine. And also, because of #3, I would recommend AGAINST having the latitude and longitude in the postal_code table: it rightly belongs in the city table, since the city won't move (and the zip code could possibly be reassigned elsewhere).