[RESOLVED] MySQL Database Design for Advertising

[dbasnett]

First let me say that I am new at MySQL / PHP.

I have a list of categories(18-20), each having sub-categories(15-40 / category). Also I have a list of headings from the yellow pages (about 4900).

I am looking for guidance about how to set the database up, and how to allow the classified ad author to use the db to assign category / sub-category / headings. Any suggestions would be appreciated.

[formlesstree4]

What you could do is have a subscriber table which contains authentication information and a unique ID [call it subscriber_id]. Then, for each of the categories, include that subscriber_id as a FK. To link the sub-categories, link them to the main category via a FK, as well as the subscriber_id as a FK. This will allow you to have multiple categories and sub-categories of the same name, but with them linked to the subscriber_id as well, meaning it's all broken down. The same goes for headings; link them to the sub-category (FK) and subscriber_id (FK).

If you need a better, or more detailed explanation, I'm more than happy to provide one.

[techgnome]

I'd have a single table for categories... all sub categories and deeper levels (incl headings) would be in the same table. I'd then have a ParentID field... when it's NULL, that's your top-level categories. Sub-categories would then have the ID of the record to which it belongs.

The ad would then have a categoryID ... which would point to this table... this allows an ad to appear at any level. If an ad can be in multiple categories or headings, then have a link table that sits between the Ad table and the Category table and has the AdID and the CategoryID.

-tg

[dbasnett]

I was certain that I replied to this... I did what techg suggested. Now if I can only figure out how to manipulate the cat / subcats on the fly... I guess java.