0

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).

0

enter image description here

Store the information in tabular format. It will also help you in validating at application level. Store your field type in property table. and values in columns table.

Hope it helps.

  • You can take a step further and Add a Column called Parent Type in AdvertType Column to do the Nesting of your Advert Types. (i.e. Electronics can be Parent Type of Advert type of Mobile.) – mouliin Aug 27 '15 at 14:03
  • Thank you, I can get a good idea of what to do. So if there is a Parent Type to the Parent Type, do i add another column? for eg. If Adver type is for android phones and its parent type is Mobile and Mobile's parent type is Electronics. – Deeyo Aug 27 '15 at 14:24
  • You don't really need to, you can write your queries in a way that you can find out parent's parent. because what if you have something with nested level of 10 ? you don't want to add 10 extra columns. – mouliin Aug 27 '15 at 15:26
0

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   -----> advert_id
advert_type   |    make
caption       |    manufacturer
description   |
owner         |    ad_detail_book
price         |    ==============
              |--> advert_id
              |    isbn
              |    binding
              |
              |    ad_detail_kitten
              |    ================
              `--> 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   -----> 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.

  • Thank you for your reply, I checked "table inheritance", it is subtypes which I was considering before but it has some limitations. – Deeyo Aug 27 '15 at 14:29
0

You will need several tables to accomplish your design. One for ads, one for categories and one for the ad properties (make, manufacturer, book title, isbn, etc...).

Here is a sample design:

  • Table Advertisement. This will contain the ad instances.

    1. ID - primary key
    2. Title
    3. Category_ID - foreign key to Category.ID. Tells us the ad category.
    4. Description
    5. Other general fields
  • Table Category. This will contain information and description about different ad categories.

    1. ID - primary key
    2. Name
    3. Description
    4. Parent_category - self-referencing foreign key to Category.ID. This enables multiple category levels.
    5. Other general fields
  • Table Property. This will contain information about all defined types of properties and to which category they belong.
    1. ID - primary key
    2. Name
    3. Description
    4. Category_ID - foreign key to Category.ID. Tells us for which category this property is relevant.
    5. Other description fields
  • Table Property_value. This will contain the actual values of the property.
    1. ID - primary key
    2. Property_ID - foreign key to Property.ID. Tells us what the property is.
    3. Advertisement_ID - foreign key to Advertisement.ID. Tells us for which ad this property is set.
    4. Value

It is quite a few tables and if you want to have pre-set values ( for choosing from drop-downs, etc. ) you will need to make some changes. If you need to have many-to-many or one-to-many relationships somewhere ( for example one ad belonging to multiple categories or one property relevant for multiple categories ) you will need additional tables to maintain the relationships.

Most probably there is a more efficient design, but this should get you started.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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