My cats layout:
| ID | cat_name | main_cat |
|---|---|---|
| 1 | vehicles | 0 |
| 2 | bikes | 1 |
| 3 | cars | 1 |
| 4 | Real estate | 0 |
| 5 | Condo's | 4 |
My Posts layout:
| ID | ad_title | ad_price | ad_sub_cat | some more |
|---|---|---|---|---|
| 1 | test title | 200 | 3 | etc: |
My Posts attributes:
| ID | ad_id | att_cat | ad_att |
|---|---|---|---|
| 1 | 1 | 3 | 5000km |
| 1 | 1 | 3 | 2009 |
| 1 | 1 | 3 | Toyota |
Okay as you can see this is a clean and simple database layout users can post different types of posts from houses to cars etc.. that's why we have a posts_attributes table that is linked to the type of category so that we can display it like this
Km: 5000km | Year: 2009 | Brand: Toyota
I can't think of a better database schema but we lead into a problem. i was building the search page and while i was doing the advanced filter I realized i don't fully know how to search for the km or the type of brand or the year i know about INNER JOIN but i am not sure how to search this kind of data is it possible for anyone to help me understand how i can search these two tables for the type of brand or km range etc...
here is my current SQL query:
SELECT * FROM `posts` WHERE `ad_title` LIKE CONCAT('%',?,'%') AND `ad_sub_cat` = ?
AND `ad_brand` LIKE CONCAT('%',?,'%') AND `ad_price` >= ? AND `ad_price` <= ?
If this post needs improvement etc. please tell me and i will fix anything or clarify what you are asking :) have a nice day
CodePudding user response：
The DB schema you've opted for is quite right. This is referred as storing MetaData, where the child table has a foreign key linked to the parent table's id and 2 columns, conventionally named with meta_key and another with meta_value.
meta_key: This column indicates what it holds, simple. Could be anything like post_views, post_bg_color, post_template and so on.
meta_value: For post_views the value would be 1,20,000, post_bg_color would be red, post_template would be just full_layout
You can extend this to whatever extent you want beyond just storing post related values.
Wordpress already uses this DB structure and is widely used by several companies.
Refs:
