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.

closed as too broad by Mark Storey-Smith, Jack Douglas Jul 28 '14 at 12:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Didn't you post this here or on another stack exchange site recently? Pretty sure I saw just this... – Craig Ringer Jul 26 '14 at 12:01
  • nope, this is my first post about this – PlacidH Jul 26 '14 at 12:13
  • WRT subcategories, it sounds like they are like classes and subclasses in OOP. If so, check out the answers to This question. – Walter Mitty Jul 26 '14 at 18:34
  • I think the minimum you need to do to make this less broad is provide a SQLFiddle. – Jack Douglas Jul 28 '14 at 12:21

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.