Database structure for an ad site

Question

I am creating an ad site similar to Quickr.com.

The main problem is that each category requires a different set of properties. For example, for a mobile phone, attributes can be the manufacturer, operating system, touch screen, whether 3G is enabled, etc. While for an apartment the attributes are the number of bedrooms, it is furnished, which floor, total area, etc. Since attributes and the number of attributes change for each category, I store the attributes and their values ​​in separate tables.

My current database structure

Ad_ads table

This table stores all ads. One entry per ad.

ad_id
ad_title
ad_desc
ad_created_on
cat_id

Data examples

----------------------------------------------------------------------------------------------- |ad_id | ad_title | ad_desc | ad_created_on | cat_id | ----------------------------------------------------------------------------------------------- |1 | Nokia Phone | Nokia n97 phone for sale. Excellent condition | <timestamp> | 2 | ----------------------------------------------------------------------------------------------- 

Cat_ad table

This table stores all available categories. The cat_id in the ads_ads table refers to the cat_id in this table.

cat_id
category
parent_cid

Data examples

 ------------------------------------------- |cat_id| category | parent_cid | ------------------------------------------- |1 | Electronics | NULL | |2 | Mobile Phone | 1 | |3 | Apartments | NULL | |4 | Apartments - Sale | 3 | ------------------------------------------- 

Response_Ad Table

This table contains all the available attributes for a specific category. Refers to the ads_cat table.

attr_id
cat_id
input_type
attr_label
attr_name

Data examples

 ----------------------------------------------------------- |attr_id | cat_id | attr_label | attr_name | ----------------------------------------------------------- |1 | 2 | Operating System | Operating_System | |2 | 2 | Is Touch Screen | Touch_Screen | |3 | 2 | Manufacturer | Manufacturer | |4 | 3 | Bedrooms | Bedrooms | |5 | 3 | Total Area | Area | |6 | 3 | Posted By | Posted_By | ----------------------------------------------------------- 

Ad table_attr_value

This table stores the attribute value for each ad in the ads_ads table.

attr_val_id attr_id ad_id attr_val

Data examples

 --------------------------------------------- |attr_val_id | attr_id | ad_id | attr_val | --------------------------------------------- |1 | 1 | 1 | Symbian OS | |2 | 2 | 1 | 1 | |3 | 3 | 1 | Nokia | --------------------------------------------- 

========

• Is this design okay?
• Is it possible to index this data with solr?
• How to fax search this data?
• Does MySQL support a merge field like solr?

Answer 1

Your design is fine, although I doubt why you use hierarchical categories. I understand that you want to organize categories from an end-user perspective. The hierarchy helps them move to the category they are looking for. However, your schema allows you to use attribute values ​​at each level. I would suggest that you only need (or perhaps) sheet-level attributes.

Of course, you can come up with attributes that will be applied at higher levels, but this will greatly complicate data management, since you will have to spend a lot of time thinking about how high the chain belongs to a certain attribute and is there any reason which the lower level may be an exception to the rule of the parent, etc.

It also, of course, complicates your regeneration too much, which, in my opinion, is part of the reason for your question.

I would suggest creating an additional table that will be used to manage the hierarchy of categories above the sheet level. It will look exactly like your classifieds_cat table, except that relationship involution will obviously be associated with the new table. Then classifieds_cat.parent_cid turns into FK in a new table, and not in the FK involution to classifieds_cat .

I think this schema change will reduce the complexity of the application and data management.

Answer 2

My suggestion is to remove cat_id from the classifieds_attribute table and then create a new table.

The new table will look like this:

 cat_attr | id | cat_id | attr_id 

This should help you reduce redundancy.