I am building a classified ads website, similar to craigslist.
The site is divided in to several sections; i.e. Forums, For Sale, Services Offered, etc.
Under each Section there are several [categories], i.e.Forums[pets], Forums[Books], For Sale[barter], Services[Barter], etc. (Notice that some categories are only uniquely identified by their section, such as in the case with barter and barter from two sections "For Sale" and "Services".)
Users will post to the categories from post links within each section. Users can upload photos and select certain amenities for their products, if applicable. A forum post will not need an amenity attribute whereas a Vehicle Ad might. Amenities include: auto transmission for vehicle ads, or furnished for housing rentals.
I am trying to figure the best logical setup for the database schema.
Currently I have this type of logical structure for basic input/query:
SECTIONS TABLE- section_id, section
CATEGORIES TABLE- cat-id, category, section_id(foreign key)
AMENITIES TABLE- amen_id, amenity
PHOTOS TABLE- photos-id, file
POST TABLE- post_id, category, timestamp, description
SECTION_POST TABLE- section_id, post_id
POST_AMENITY TABLE- post_id, amenity_id
POST_PHOTO TABLE- post_id, photo_id
I made the [SECTION_POST] my main many-to-many because the category in the [POST] must be related to the section. I related CATEGORY to SECTION in the categories table, which looks to me like a M-to-M with the addition of category attribute. Is this ok?
Also, do you have any other suggestions as to how i should be thinking on this schema? I think the problem I am having is mostly related to ignorance not lack of organizational skills. Maybe one of you can educate me or refer me to a decent link that tackles my general problem.