[thelist] Zip Code mysql db

rudy r937 at interlog.com
Sun Apr 14 06:14:01 CDT 2002


>  CREATE TABLE zip_code (
>     zip_code varchar (15)
>  PRIMARY KEY (zip_code)
>
> and would this serve as lookup table?

hi russell

the way you have it there, it would not be very useful without another data
column that the zip code translates into

a lookup table typically uses a code as the primary key, and for each code
value, provides some other value, like a description

in your case you'd want the zip to be associated with lat/long

did you investigate any of the lat/long solutions suggested the other day?

> Any other comments appreciated
>
> CREATE TABLE city (
>   city_id mediumint(9) DEFAULT '0' NOT NULL auto_increment,
>   name varchar(75) NOT NULL,
>   latitude float(7,5) DEFAULT '0.00000' NOT NULL,
>   longitude float(7,5) DEFAULT '0.00000' NOT NULL,
>   state_id tinyint(4) DEFAULT '0' NOT NULL,
>   PRIMARY KEY (city_id),
>   KEY name (name),
>   KEY latitude (latitude),
>   KEY longitude (longitude),
>   KEY state_id (state_id)

i think the lat/long should move to the zip table

also, it's a good idea to use the INDEX keyword instead of KEY, which is
unique to mysql

you would typically not index the name and state_id

state_id would probably be better as the 2-char code, rather than an
integer

and if you're going to declare something as a numeric datatype, be careful
assigning it a default value of '0' which is a string -- this is the kind
of rigour you must bring to databases


rudy





More information about the thelist mailing list