MySQL Database Design for City/State

citydatabaseMySQLstatezipcode

I am building a site where there will be a bunch of companies and their business listing, so they will have their Address, City, State, Zip Code, etc.

I found this website here:
http://jesseprice.com/mysql-city-state-zip-latitude-longitude-database/ which has a database of all city, state, zip, county, lat, long.

I imported the database above, so my question is how would I connect this to my companies table? And when a company signs up, I guess I could make a drop down for the state, and then it will drop down to the cities found in that state pulling from the database so they choose from the those, and then the zip code. Would that work? So they wouldn't have to enter it in themselves?

I'm just a little confused on how to do this properly and all make it work together.

Thank you.

Best Answer

Option 1:

Add ALL fields to your address table, referencing the selected table on additions, placing city, state, zip, county, lat and long into your table.

Advantages:

You can provide international support without making updates to the downloaded table.
You can update the downloaded table without loss of data (unlikely)
All data is available in one single-table query.
lat/lon searches should be faster.
Allows for flexibility in addresses that may not be available in the downloaded table.

Option 2:

Add a zip field only, and for all lookups, pull the data from the downloaded table.

Advantages:

Normalized - all records are in one table. Less drive space.

I would actually recommend adding the fields into your managed table, and keep the downloaded table as a read-only, replaceable reference table.

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Mysql – Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Mysql – Which MySQL data type to use for storing boolean values

Related Topic