Monday, April 11, 2016

Database design for classified ad item specification

By Hường Hana 8:30 PM , , Leave a Comment

I'm working on a classified ads site with 12 categories. E.g. category vehicles has items cars, bikes, Commercial Vehicles and spare parts. The following is a flow diagram for posting an ad:

When user want to post an ad

I need to show the specification in the Form Filled section of the above image to the users in dropdown lists in the form when they are posting an advertisement. The car specification will be its color,engine,fuel type.

The ERD is below :

ERD

How should this issue be tackled, what are the best practices and is the current design going along the right lines?

4 Answers

Answers 1

On the whole this looks ok. Here are some observations:

  1. likes.iker_id should point at users.id? Just trying to understand your model to start.
  2. I would probably change the pics table to be one pic per row and then add an ordinal for ordering.
  3. One question here is how you intend to look at your graph model. As it is, you might have a graph that could be traversed easily to a depth, a couple deep. I assume you are doing this to recommend ads. If so, I think this is sufficient. If not it would be good to further discuss which rdbms you are targetting.

Answers 2

Hope this helps:
In a simplified case, you will need some extra tables.

enter image description here

Answers 3

So, you are trying to be able to have different specifications for different items in your categories? Or, in other words, it is like having different attributes for different types of products in an e-commerce website.

If that the problem you are tackling, then you should look into the Entity–Attribute–Value (EAV) model that is how the problem is solved. By the way, one of the most popular open source e-commerce engines uses it as well.

enter image description here

Answers 4

i agree look at EAV models...

for some other tables, you have many normalization issues - for example:

  • you should have a separate address table (not part of the ad)
  • you should have a picture table (and link those to the ads with another table)
  • you should have a person table - and link that to the ad as 'owner'
  • the idea of 'favorite' should also be in this person->ad relationship table as a role or type column
If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment