title: Database structure for a classifieds website
body: <p>I am developing a classifieds website similar to Quickr.com.</p>
  
  <p>The main problem is that each category requires a different set of properties. For example, for a mobile phone the attributes might be Manufacturer, Operating System, Is Touch Screen, Is 3G enabled etc... Whereas for an apartment the attributes are Number of bedrooms, Is furnished, Which floor, total area etc. Since the attributes and the number of attributes varies for each category, I am keeping the attributes and their values in separate tables.</p>
  
  <p>My current database structure is</p>
  
  <h2>Table classifieds_ads</h2>
  
  <p>This table stores all the ads. One record per ad.</p>
  
  <p><strong>ad_id</strong><br>
  ad_title<br>
  ad_desc<br>
  ad_created_on<br>
  cat_id</p>
  
  <p><strong>Sample data</strong> </p>
  
  <pre><code>-----------------------------------------------------------------------------------------------
  |ad_id | ad_title    | ad_desc                                       | ad_created_on | cat_id |
  -----------------------------------------------------------------------------------------------
  |1     | Nokia Phone | Nokia n97 phone for sale. Excellent condition | &lt;timestamp&gt;   | 2      |
  -----------------------------------------------------------------------------------------------
  </code></pre>
  
  <h2>Table classifieds_cat</h2>
  
  <p>This table stores all the available category. cat_id in classifieds_ads table relates to cat_id in this table.</p>
  
  <p><strong>cat_id</strong><br>
  category<br>
  parent_cid  </p>
  
  <p><strong>Sample data</strong></p>
  
  <pre><code>-------------------------------------------
  |cat_id| category            | parent_cid |
  -------------------------------------------
  |1     | Electronics         | NULL       |
  |2     | Mobile Phone        | 1          |
  |3     | Apartments          | NULL       |
  |4     | Apartments - Sale   | 3          |
  -------------------------------------------
  </code></pre>
  
  <h2>Table classifieds_attribute</h2>
  
  <p>This table contains all the available attributes for a particular category. Relates to classifieds_cat table.</p>
  
  <p><strong>attr_id</strong><br>
  cat_id<br>
  input_type<br>
  attr_label<br>
  attr_name   </p>
  
  <p><strong>Sample data</strong> </p>
  
  <pre><code>-----------------------------------------------------------
  |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          |
  -----------------------------------------------------------
  </code></pre>
  
  <h2>Table classifieds_attr_value</h2>
  
  <p>This table stores the attribute value for each ad in classifieds_ads table.</p>
  
  <p><strong>attr_val_id</strong>
  attr_id
  ad_id
  attr_val</p>
  
  <p><strong>Sample data</strong> </p>
  
  <pre><code>---------------------------------------------
  |attr_val_id | attr_id | ad_id | attr_val   |
  ---------------------------------------------
  |1           | 1       | 1     | Symbian OS |
  |2           | 2       | 1     | 1          |
  |3           | 3       | 1     | Nokia      |
  ---------------------------------------------
  </code></pre>
  
  <p>========</p>
  
  <ul>
  <li>Is this design okay?</li>
  <li>Is it possible to index this data with solr?</li>
  <li>How can I perform a faceted search on this data?</li>
  <li>Does MySQL support field collapsing like solr?</li>
  </ul>