CODEWITHSUNDEEP
mysqldatabaseweb-applicationsdatabase-design
Sandy
Sandy

Reputation: 1012

DB design clarification for developing classified website

I am about to start developing a classified web application. but I am facing certain difficulties while building the DB design:

User can post their advertisements under different types of pre-existing catagories (like Vehicles, Real Estate, Computers, Education etc). But each category have their certain specific fields/properties as well as some common fields/properties.

My difficulties is like after filling a post Ad, say for Bike, I am saving the common i/p values to my advertisement_t table (as below). but where will I store other fields values specific to Bike like: Bike Manufacture, Bike Model, Bike Year etc?

same way, if a user wanted to post an advertisement for House(rent), I can save the common i/p values to my advertisement_t table. but where will I store other fields values specific to House For Rent like: total area(sq), No of Rooms, etc. and how will I map.

My Tables are below:

category_id
------------
(catId, parentCatId, cat_title, cat_desc)

advertisement_t
----------------
(adId, catId, userId, ad_title, ad_desc, photoId, postDate, statusId, price, ad_address, adValidFrom, adValidTo, adIsDeleted) 

Photo_t
--------
(photoId, primary_photo, sec_photo1, sec_photo_2, sec_photo_3)

user_t
-------
(userId, username, password, email, activationkey, isvalidated, joinedDate, activatedDate, statusId)

I dont have any idea how other classified sites(like olx, freeadds) used to maintain(persist & display) their posted advertisement data on their database.

If any one has any idea, please help.

Thanks in advance.

Upvotes: 1

Views: 1238

Answers (2)

Raul Cardoso
Raul Cardoso

Reputation: 38

I did something close to this Once.

And I can see 2 options: Something you shouldn't do - Implement lots of tables for the required Goods (Houses, Cars, Bikes,..) which is not a very good idea because work would never end and it wouldn't be extensible.

My suggestion:

Add 3 more tables:

CustomField (To set a list of possible fields used by categories)

(CFid, Title)

CategoryCField (Associative relation to know what fields to display when user enters a new ad)

(catId, CFid)

StoredCFData (Actually store the Data)

(id, adId, CFid, value)

You can find however 2 problems doing it this way. Making a "relation circle" which is not very good, but if carefully done will do the work. And having to reduce the DataType of costum fields to text/numbers to make it simple. You'll have troubles applying it to Radio buttons or Combo Boxes.

Best Luck, Raul

Upvotes: 0

georgecj11
georgecj11

Reputation: 1637

I usually like classify these data into 2 groups (1) searchable data which will be saved in some column/mapped to column and (2) junk data( i call that additional_info) saved in a text column in json_encoded format.

For searchable columns, you can add another table category_values table, which has (category_id, category_value, ad_id)

Again, if your DB is expected to grow huge with category type repeating often, you can break the category_values table to two as (value_id, Category_id, category_value) and (value_id, ad_id). This results in more joins and need not be introduced in you case I believe.

Upvotes: 1

Related Questions