CODEWITHSUNDEEP
databasedatabase-designrelational-database
Gammer
Gammer

Reputation: 5628

Database design for classified ad item specification

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?

Upvotes: 3

Views: 4369

Answers (4)

Randy
Randy

Reputation: 16673

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

Upvotes: 0

Uzbekjon
Uzbekjon

Reputation: 11823

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

Upvotes: 1

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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

enter image description here

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 2

Related Questions