[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