Help With Database Layout

[$Three3]

Hello everyone, I am working on a site similar to Craigslist where users can make postings and sell items in different cities. One difference between my site and Craigslist will be you will be able to search by zip code instead of having all of the cities listed on the page.

 

I already have the ZIP Code database that has all of the city, state, latitude, longitude, and zip code info for each city. Okay, so to dive into what I need done and what I need help with:

 

1.) Although I have the ZIP Code database, it is not setup perfectly for my use. (I downloaded it off of the internet for free from http://zips.sourceforge.net/)

2.) I need help setting up my database structure (Ex: How many different tables should I use and how should I link them)

 

I will be using PHP and MySQL.

 

These our my thoughts so far on how the database can be setup: (I am not sure if this will work though.)

 

Scenario:

 

Someone goes to the homepage and it will tell them, "Please enter your ZIP Code.". If they enter "17241" for example, this ZIP Code is for a city named Newville located in Pennsylvania. The query would look like this with the current database setup:

 

SELECT city FROM zip_codes WHERE zip = 17241;

 

The result of the query would be "Newville".  The problem I see here now is when they want to post something in the Newville section of the site, I will have to have an entire table setup just for the Newville city postings. There are over 42,000 cities which means I would have to have over 42,000 tables (one for each city) so that would be insane to have to do it that way. One way I was thinking of doing it was to add a column to the ZIP Code database called "city_id" which would be a unique number assigned to each city. So for example, the city Newville would have a city_id of 83. So now if someone comes and post a listing in the city Newville I would only need one other table. That one other table would be setup like this:

 

CREATE TABLE postings (
posting_id INT NOT NULL AUTO_INCREMENT,
for_sale LONGTEXT NULL,
for_sale_date DATETIME NULL,
for_sale_city_id INT NULL,
jobs LONGTEXT NULL,
jobs_date DATETIME NULL,
jobs_city_id INT NULL,
PRIMARY KEY(posting_id)
);

 

(The for_sale and job_ column names are categories of the types of postings users will be able to list under. There will be many more categories than just those two but this is just for example.)

 

So now when when someone comes to the website and they are looking for something to buy and not sell, they can enter their ZIP Code, 17241, for example, and this is the query that will run:

 

SELECT city, city_id FROM zip_codes WHERE zip = 17241; //Result: Newville 83

 

(Please note that I will be using PHP to store the ZIP Code the user enters in SESSIONS and Cookies to remember them throughout the site)

 

Now it will tell them, "Please choose your category.". If they choose the category "Items For Sale" then this is the query to run and sort the results:

 

SELECT posting_id, for_sale, for_sale_date FROM postings WHERE for_sale_city_id = $_SESSION['zip_code'];

 

Will this work?

 

So now my question to everyone is will this work? I am pretty sure it will but I do not want to set this thing up and realize I overlooked something and have to start from all over from scratch. Any opinions and ideas are welcomed and I will listen to anyone who has some thoughts. I really appreciate the help in advance

[MatthewJ]

I would recommend reading an in-depth book about database design. You are going to to build in some problems from the ground up if you just start stuffing things in to the db . Not that I don't think there are plenty of people here that could help, but a proper response is just going to be long.

[tomfmason]

I would use distance from the given zip instead of the actual zip. This would make it easier for your users to find postings close to them. The chances of someone finding one within their zip would be pretty low unless they live in an urban area. Here is an example of finding the distance between two sets of long/lats in mysql - http://www.imranulhoque.com/mysql/mysql-function-to-find-distance-between-two-places-using-latlong/

[tomfmason]

I got bored and decided to mess around with this a bit. First I downloaded the same db that you have from sourceforge. Then I had to modify the function that I linked to in my earlier post.

 

The mysql function(modified for the db from sf)

DELIMITER $$

DROP FUNCTION IF EXISTS `GetDistance`$$

CREATE FUNCTION `GetDistance`(lat VARCHAR(120), lon VARCHAR(120), lati VARCHAR(120), longg VARCHAR(120))
RETURNS VARCHAR(120)
BEGIN
DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(12,;

select CAST(lon as DECIMAL(12,) into lon1;
select CAST(lat as DECIMAL(12,) into lat1;
select CAST(longg as DECIMAL(12,) into lon2;
select CAST(lati as DECIMAL(12,) into lat2;
select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;
RETURN distance;

END$$

DELIMITER ;

 

and here is the simple proof of concept(in no way production worthy)

 

<?php
$link = mysql_connect('host', 'user', 'pass');
mysql_select_db('db');

$zip = $_GET['zip'];
$distance = $_GET['distance'];

$s = "select latitude,longitude from zip_codes where zip='$zip'";
$r = mysql_query($s);
$cords = mysql_fetch_assoc($r);

$sql = "select * from `zip_codes` where GetDistance('{$cords['latitude']}','{$cords['longitude']}',latitude,longitude) <= $distance;";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
echo $row['zip'] . " ";
echo $row['state'] . " ";
echo $row['latitude'] . " ";
echo $row['longitude'] . " ";
echo $row['city'] . " ";
echo $row['full_state'] . "<br />";
}
mysql_close($link);
?>

 

The code above is in no way production worthy. It was only meant as a proof of concept and should not be used where anyone other than the developer has access to.

 

 

I used the zip from your example (17241) and the distance of 10 ie zip=17241&distance=10

 

output

17081 PA  40.202404  -77.28825 Plainfield Pennsylvania

170XX PA  40.263330  -77.54278  Pennsylvania

17240 PA  40.147267  -77.57259 Newburg Pennsylvania

17241 PA  40.172412  -77.40826 Newville Pennsylvania

17257 PA  40.053308  -77.50622 Shippensburg Pennsylvania

17266 PA  40.086522  -77.41026 Walnut Bottom Pennsylvania