How to design a Table in which its fields varies on its categories? I am trying to design a database to post free ads. Eg http://www.gumtree.com.au/p-post-ad.html

Scenario: Every ad will have different categories and some categories will have further sub categories. SO the table AD will have different fields depending on its category. For example, an ad for used car will have MAKE, MANUFACTURER etc but an ad for books will have Book title field, Author field etc.

SO how do I design the table for ads?

I can possibly think of two solutions. One, to create subtypes for categories in table ad. Two, to create individual table for each categories of ad (this does not sound like a good design).
I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows.

The usual technique for this sort of problem is "table inheritance" (so search that term for more examples, whereby you have one core table that contains all the common properties (in this case things like date, caption, description and owner), a column to indicate the type of advert and an extra table for each type. So for a car the ad_type column would contain "car" and the details would be found ad_detail_car table. Something like:

ad_common          ad_detail_car
===========        =============
advert_id   -----&gt; advert_id
advert_type   |    make
caption       |    manufacturer
description   |
owner         |    ad_detail_book
price         |    ==============
              |--&gt; advert_id
              |    isbn
              |    binding
              |
              |    ad_detail_kitten
              |    ================
              `--&gt; advert_id
                   breed
                   age


advert_id would be the PK for the common table and both the PK and an FK to the common table in the detail tables.

As the number of advert types grows having a separate table for each might become a mess so you might instead consider a property bag like so:

ad_common          ad_extra_properties
===========        ===================
advert_id   -----&gt; advert_id
advert_type        property_name
caption            property_type
description        property_value
owner             
price             


Here the PK for the property bag is advert_id,property_name. Be warned though, this is a pattern (called the EAV or "Entity Attribute Value" pattern) that has its own set of problems, particularly for reporting, so read around that before considering if it is suitable for your needs.

Another option if going for the EAV model is to do away with the detail or property bag tables completely and just store the extra properties in a format useful for the business logic (JSON, XML) in a column of the main table. This is simpler but means (unless your chosen DB natively supports such documents) you can't report on or filter/sort by the extra properties at all directly in the database.