Hi,
I need some help in my database layout schema. Here is the scenerio... I am currently developing a realty listing web app so for each house listed there are numerous different options(e.g. does it have a pool/ ac / washer dryer amenities and so forth). Needless to say having a seperate feild in the database table seems a little, well, inefecient. Is there any way i could restrucutre/recode inorder to minimuze the usage of fields, and also retaining the ability to to search querys with the ability to filter and select certain homes with certian options (e.g. SELECT * FROM homes WHERE has_pool=1 that functionality). One method i can think of is using three tables as follows
Homes:
+------+---------+
| id | price |
+------+---------+
| 1 | 10000 |
+------+---------+
Options:
+------+---------------+
| id | option_text |
+------+---------------+
| 1 | Pool |
+------+---------------+
Home_Ops:
+------+-----------+-------------+
| id | home_id | option_id |
+------+-----------+-------------+
| 1 | 1 | 1 |
+------+-----------+-------------+
Thus allowing me to expend/decrease the availible options for each home.. and allowing me to pull data out as follows:
SELECT home_id FROM Home_Ops WHERE option_id=1
but the only problem is that say a home has matched more than one option... then i would receive two result rows for one house.. is there any way using MySql's SQL to remove that option... or how would i structure my SQL query inorder to not have that but retain the functionailty.
Thank you for your time
-Jon Bardin
I need some help in my database layout schema. Here is the scenerio... I am currently developing a realty listing web app so for each house listed there are numerous different options(e.g. does it have a pool/ ac / washer dryer amenities and so forth). Needless to say having a seperate feild in the database table seems a little, well, inefecient. Is there any way i could restrucutre/recode inorder to minimuze the usage of fields, and also retaining the ability to to search querys with the ability to filter and select certain homes with certian options (e.g. SELECT * FROM homes WHERE has_pool=1 that functionality). One method i can think of is using three tables as follows
Homes:
+------+---------+
| id | price |
+------+---------+
| 1 | 10000 |
+------+---------+
Options:
+------+---------------+
| id | option_text |
+------+---------------+
| 1 | Pool |
+------+---------------+
Home_Ops:
+------+-----------+-------------+
| id | home_id | option_id |
+------+-----------+-------------+
| 1 | 1 | 1 |
+------+-----------+-------------+
Thus allowing me to expend/decrease the availible options for each home.. and allowing me to pull data out as follows:
SELECT home_id FROM Home_Ops WHERE option_id=1
but the only problem is that say a home has matched more than one option... then i would receive two result rows for one house.. is there any way using MySql's SQL to remove that option... or how would i structure my SQL query inorder to not have that but retain the functionailty.
Thank you for your time
-Jon Bardin