CategoryName (ie, Cars, Trucks, Furniture, Clothing, etc.)
ZipCode (or maybe this should be ZipID to the Zip Code info table that will have zips, citys, and whatever else?)
Table: Autos (there would be other tables like this for Real Estate, Miscellaneous, Personals, Jobs, etc.)
FeaturePictureID (picture designated as the thumbnail on searches) Corresponds to PictureID in Pictures table.
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.)
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 1when 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!!