Postgresql – Database Design for classifieds site


I'm creating a classifieds site where there are many categories and subcategories.
1) Each category has different fields.
2) Each listing has one or more images

I need help with the database design. My current thinking is:

  • Listings table. fields: id, image_id etc.
  • A table for each category, which would contain a listing_id
  • Images table. fields: listing_id
  • I'm not sure how I'd go about doing subcategories. I'm a bit of a noob, so I may be on the wrong track altogether.

I'm using postgresql by the way.

Best Answer

This sort of question (have new project, how do I...) appears frequently here. I will urge you to do what I would urge everybody who has a new project to do - stand on the shoulders of giants.

Check out any/a few/all Open Source projects that do what you are interested in doing (or similar) and learn from their database schemas and adapt what has already been done to your own needs. A quick search gave me this site (and this one) both of which appears to be interesting from your perspective.

Take a look at them and if there's anything you're having trouble with, post back here. The only (small IMHO) issue is that both of them are MySQL based, but that shouldn't be a major stumbling block.