I hate this, what Tom Kyte calls the "funky data model". Flexible as you like, but so hard to query (and this is a very simple query!)
Also, data integrity is non-existent, because there can be no database constraint that stops you putting 2 for city or kingsley for beds...
from table1 t1, table2 beds, table2 city
where t1.id = beds.listing_id
and beds.field_name = 'beds'
and beds.field_value = '2'
and city.field_name = 'city'
and city.field_value = 'kingsley';
Originally posted by greengaint
Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again
Everyone "invents" this data model at some point early in their database careers; well, I'm sure I did once anyway. "Hey, look! With this model we don't have to alter tables or application code ever again! The users can just define their own 'fields' whenever they like!" Then later you find that:
(a) performance sucks very badly, and
(b) you need to write SQL 17 pages long to produce the simplest report, and
(c) the data is full of nonsense like beds='4.2' and city = ' kignsley' and startdate = '31 Feb 2004'... and
(d) nobody likes this system any more