[Richaun]

I'm attempting to come up with an efficient database design for a classified ad system that will display relevant ads based on zip code (ie, within 10 miles of zip code xxxxx). I haven't got zip code data yet, but I know that will be a new table. My problem is more basic - figuring out the relationships and what fields should go in which table. This is what I have after several days of pondering with minimal fields for simplicity sake: "*" denotes primary keys

Table: Users
Fields:
UserID *
UserName
Email
Password

Table: Category
Fields:
CategoryID *
CategoryName (ie, Cars, Trucks, Furniture, Clothing, etc.)
Description

Table: AdInfo
Fields:
AdID *
UserID
CategoryID
ZipCode (or maybe this should be ZipID to the Zip Code info table that will have zips, citys, and whatever else?)
StartDate
AdViews

Table: Autos (there would be other tables like this for Real Estate, Miscellaneous, Personals, Jobs, etc.)
Fields:
AdID *
Year
Make
Upholstery
Price
Description
FeaturePictureID (picture designated as the thumbnail on searches) Corresponds to PictureID in Pictures table.

Table: Pictures
Fields:
PictureID *
AdID
FullPictureURL
ThumbURL

Table: ZipCodes (I'm not sure what all is required to calculate distances from zip to zip - but this is where that stuff might go.)
Fields:
Zipcode
City
State
Latitude
Longitude

So with this layout I have to query against AdInfo for Zipcode everytime. I'm figuring prior to this that I identify what zip codes are 10 miles from the target zip code and use

SELECT Year, Make, Price, FeaturePictureID FROM Autos WHERE (AdInfo.ZipCode = '12345' OR AdInfo.ZipCode = '67890')
 AND Autos.AdID = AdInfo.AdID AND AdInfo.CategoryID = '100'

(syntax may be off... that's my first select statement in about 2 years)

Anyway, that seems way complicated! I feel like I may be over compliating things. And what do you think of how I would handle pictures with this design? Maybe the first picture added should just be the FeaturePicture, in which case I would use the

FROM Pictures WHERE AdID = 'xxx' SORT BY PictureID LIMIT 1

when querying for the thumbnail to display on an initial search page?

And a note about the CategoryID. I want to make this application as flexible as possible. What if I want to add new categories down the road? And how will I handle searching on all Autos, for example? I don't want to hard code CategoryID's in the script - I need it to be dynamic. So if I want to add Flying Cars I don't have to modify code - the search will adapt and include the new CategoryID for Flying Cars when searching all Autos. So far I've thought of using a range for like categories, like Cars - 110, Trucks -120, searching for 1xx for all Autos. Or I could have a Main Category with Sub-categies associated with it, although I'm not sure how I'd design the table for it at this point.

Any thoughts? I really appreciate your time!!

[skyhawk133]

Richaun, there are a few changes I would make, specifically, elinating the individual tables for each category (autos, real estate, jobs, etc.), and doing a properly normalized setup using 2 seperate tables, 1 table that has the descriptor_id*, category_id, and descriptor (make, model, color, etc.), then another table that has ad_id, descriptor_id, value

This allows you to have your categories, then a 1-to-many relationship between the descriptors and the categories, and in the second table a many to many relationship between the ad and the descriptor.

Hope that helps a little, it's late here and I'm not thinking 100% clearly, but I know you can't do individual tables for each category because the database isn't normalized then. You have to use 2 seperate tables to create a many to many relationship.

[Richaun]

Thanks for the reply skyhawk! Wow, it's clear how unlearned I am with database design. I've read some articles on normalization, but I think I'm having trouble grasping it because this is my 2nd time around on this design. With my first I had 1 table for each big category - Autos, Real Estate, General, and Free. (And of course a users table.) A big problem with it was that I had close to 100 fields in some tables per row! And not all fields applied to all ads in that table (like Motorcycles can't have doors, etc.)

But anyway, I think I see what you're introducing to me. I just couldn't see how I could put everything together when it's obvious I'm going to have a variety of items that have very different descriptors. Can you help with forming the queries to extract the right data efficiently? You've proposed:

Table: Fields
Fields:
Descriptor_ID *
Category_ID
Descriptor

Table: Values
Fields:
Ad_ID *
Descriptor_ID *
Value

So I'm thinking about an initial search on just one category to keep it simple. I'd also need a Category table:

Table: Category
Fields:
Category_ID *
CategoryName

1.) Display the form -- provide a drop-down menu of available categories (CategoryName) from Category table.
2.) User selects "Motorcycles" -- now we have a Category_ID to query with.
3.) Now the part I'm having trouble envisioning. On the initial search, we don't want to show all of the fields. We just want to show a copule of things, ie. thumbnail, year, make, model, price, and a little of the description. We'll show all information once that ad is opened, including additional pictures. Does this still sound like these 3 tables is what should be used? Because I think I can do it with these tables... it's just a big change of thinking!

Thanks for your suggestions!! P.S. Anyone know of any articles that may help me grasp these fundamentals? I've read several different renditions of 1st, 2nd, and 3rd normalizations already. Could be thick-skull syndrome I guess...

[Jayman]

Normalization is a critical first step for designing a database. It insures that data redundancy has been elliminated in a database. You will find that there are 3 steps to normalize a database, these steps are 1NF, 2NF and 3NF. Actually there are 4 steps, but rarely does a database designer ever need the 4th step. Usually by the 3rd step normalization has been established.

http://databases.abo...cts/index_r.htm
http://databases.abo...rmalization.htm
http://databases.abo...ducts/a/2nf.htm
http://databases.abo...cts/l/aa3nf.htm

[Trogdor]

Is there a reason the pictures are seperated from the adds-reccords?
If the above(somewhere) query is used for image selection then you will essentialy have a 1 on 1 relation between adds and pictures, and it is much more efficient to put them together into one table.


Also the zipcode part will have a big impact.
I envision you will do something like:
- get the (center) zipcode
- convert the postcode into lat-long
- select all postcodes that are in a 10 k radius using a distance formula on the lat-long fields.

This will mean sequential scans on the postcode table for each product search, unless you do _very_ smart things with indexes.

Another option might be to pre-generate a table containing rounded lat-long pairs (essentialy a grid) + a linked table that denotes what postcodes are within 'reach'

These tables might looks something like: (*=primary index, +=secondary index)
Table latlongGrid:
latlongID *
from-lat +
to-lat +
from-long +
to-long +

Table GridToZipcodes:
latlongID + (non unique)
Zipcode
Distance +

I included the distance so that in a later stage you can vary the radius (up unto the maximum radius that was recorded in that table in the pregeneration stage)

In that schema you will end up with a quite straightforward set of lookups that you can use for a direct join (on Zipcode) to your products.
The only real work will be the pre-generation of the grid and the GridToZipcodes table.

[EDIT]

After a night of sleep it apeared to me that you can make the pregenerated distances table in a simpeler way.

Table Distances:
Zipcode *
ToZipcode
Distance +

You can generate the content of this table by using the longitude / lattitude information in the zipcode and making the cartesian product, limiting on the distance calculated.

This post has been edited by Trogdor: 11 October 2006 - 02:37 AM