January 18th, 2004, 07:07 PM
normalised design causing problems displaying results
I s'pose this is more of a database design query.
I have a database holding information about properties, along with what features these houses have, eg. number of bedrooms, type of property (eg. detatched, terraced), garden, etc. My client would like to be able to add more features in the future through the admin section without having to ask us o modify the web site.
If these features weren't going to change, i'd have a column for each of these features in the main property table. I've had to fix some databases where the programmer allowed the user to add/delete columns before, so I think that's a bad route to go down. So i'm reckoning on a table with questions, eg. number of bedrooms, an answer table, with all of the answers for each question, and an intermediate table between the answers table and the main property table, which holds the which answers apply for the property.
The only trouble with this method is that to do a property search, the results have several records for each property due to joins and one-to-many relationships, which makes paging on the asp page very difficult and probably very inefficient. Using ado paging for this just will not work, because the number of results returned bears no relation to how many actual properties there are.
Has anyone gt any suggestions? I've been trawling the net for answers with no success. I can't believe no-one's had this problem before!
January 18th, 2004, 09:31 PM
Re: normalised design causing problems displaying results
yes, set up a table for the "attributes" like bedrooms, driveways, does it have a roof or not, etc.
if you do it right, those will be values in a column, totally generic, the user/administrator will simply be adding data when it is necessary to incorporate a new attribute into the structure of a property
perhaps this thread on, um, another forum may be of assistance
January 19th, 2004, 06:40 AM
yes, i've set it up like that, the problem is paging through results and displaying X number of results were found.
i tried clicking your link, but it just redirected me to the main forum index and i don't seem to be able to find the topic.
Deadline for the whole web site is thursday so i'm starting to panic now
January 19th, 2004, 07:01 AM
right i found the forum topic.
it mentions limiting the results, but doesn't really touch on how to solve the problem of multiple rows being returned for each property.
Deadline's creeping closer and i'm disgusted with myself that i'm gonna have to fudge this one. I was also suprised how difficult it was to find out about this method of setting up a DB and then actually using the data.
January 19th, 2004, 08:31 AM
there is no "problem" of multiple rows for each property -- that's the way it's designed
if you want to page, then yes, i agree, the multiple rows make the queries trickier
this thread (on this site) shows how to create "prev" and "next" links
you are using micrososft sql server, so subqueries are allowed
here's a sample of data that might be returned:
these results would come from this type of query:
property | attribute | value
123 Pine St | bathrooms | 2
123 Pine St | pricerange | high
123 Pine St | takeback2nd | yes
5 Elm Lane | bathrooms | 1
5 Elm Lane | bedrooms | 1
15 Oak Terr | bedrooms | 7
15 Oak Terr | garage | 2
15 Oak Terr | heating | oil
notice that the subquery does the filtering of properties based on chosen attribute values, while the outer query returns all attribute values for each property
where property in
( select property
where attribute = X1 and value = Y1
or attribute = X2 and value = Y2
or attribute = X3 and value = Y3
having count(*) = N
in order to do paging, you'll want to use top 10 on the subquery, and use another subquery within that with where propertyid > currid
January 19th, 2004, 10:54 AM
hmmm...i had 4 tables involved: property_tbl, option_tbl, property_option_tbl, features_tbl.
Each record in the feature_tbl has several rows in the option_tbl...this is so i can use these as a lookup for dropdowns in forms. Then for each feature fo the property there would be an option_id and a property_id in the property_option_tbl.
And then I joined all these with inner joins, to get exactly what you get with your results.
How can u work out how many pages of properties there are if you don't know how many properties were returned? I've managed this before, but only because i was just doing simple next/prev paging. I know how to do next/prev usign the id...my problem is that the client wants:
X records found, page 1|2|3|5, showing page 3.
And yes normally i know how to do that also, but the duplicate records for each property is what has me stumped, as the number of records returned bears no relation to the number of properties returned. And without running the entire query again but selecting only DISTINCT property_id, or even cycling through the results keeping a count of property_id's before I display them later in the page I don't know what else to do. I haven't gone down either of those routes cos I don't like them.
Last edited by emilyb; January 19th, 2004 at 11:00 AM.
January 19th, 2004, 11:15 AM
there is no way to know how many properties satisfy the selection criteria (number of bathrooms > 3, etc.) unless you run the query that finds all such properties
but you would only have to do this once...
... which you can actually do quite easily, the first time the query is run
in other words, when a new set of selection criteria is entered, run the entire query without the TOP restriction, so that you can use RecordCount (or whatever the function is called in your scripting language) to tell you how many records were returned, even though you show only the first 10 or whatever
then when you call "next" you can pass the number
January 19th, 2004, 11:16 AM
oh, wait, i just realized you were using
1 | 2 | 3 | ...
and presumably these would each be a link to Page 1, Page 2, etc.?
a bit trickier, that
January 19th, 2004, 11:21 AM
erm yes indeed!
January 19th, 2004, 11:26 AM
change the user's expectations
January 19th, 2004, 11:42 AM
yeah i nearly put that on my last post!
answer: convince the client they don't want it!
Doh! I think i might go and get and job stacking supermarket shelves instead
January 19th, 2004, 11:55 AM
no, show the client the paging links that you feel comfortable implementing
heck, even google does "Results 1-10 of approx 26,000"
easy links at the bottom would be First, Prev, Next, and Last
anything else is too tough
now, if the user balks, tell the user about nested subqueries, and preprocessing the entire result set every time, and the performance penalty this implies for the site visitors (and watch their eyes glaze over...)
or do it the smart way: something along the lines of "sure, i can do it the way you want, no problem, but it requires a lot of extra programming, i'd estimate at least an extra ten hours or so, and as you know, i bill $100/hour, so it's up to you, cher client..."
January 21st, 2004, 01:02 AM
Why bring back a row for each piece of criteria queried? If the user entered the search criteria they know what the values will be. Just bring back the Propterty (House) and simply page through the results using one of the hundred ways of paging in ASP.
Clicking the property could drill into the detail which would show all the values for the custom attributes.
Just a thought.
January 21st, 2004, 06:23 AM
adam, in general, you would be right, but then again, maybe not
if i ran a search for a property based on these criteria:
1) more than 3 bedrooms
2) vendor will take back a 2nd mortgage
and all i got back was a list of houses with their street addresses, and i had to click on each one to find out anything else about them, then i'd be peeved, wouldn't i
but then, that's just me
January 21st, 2004, 07:02 PM
yep so would i....users need at least a brief description of the property in order to even know which ones they want to click on to find out more....
there are loads of national property web sites in the uk....but i have a sneaky suspicion they don' bother having such a system as we've been discussing....and perhaps what i'm doing is over-elaborate for what it is, maybe. I think some of the big property sites just use a free-text field for all the features of a property other than price, number of bedrooms, location and type of property (eg detached, bungalow - do u even have bungalows in canada and US? ), but that way users can't do an advanced search based on more in-depth criteria, which i found really annoying when i was hunting for my house.
And besides, I really feel like i'm not doing myself justice if i turn round to my boss/client and say "no you cacn't have that it's too hard". I'd never learn anything new. And, probably a downfall, I'm a perfectionist but that's not always a good thing!
My solution though...i fudged it the deadline was short! it's a combination of my first database design (mainly because each available option for each feature has to have a % weighting in order to calculate an estimate of the value of the property - a wacky idea since a lot of that has human elements involved in it but hey the client wanted it!) and some cheating to make coding easier. If i can find a way of doing it in the future i might come back to it, if only for my own sanity!
Thanks for all your help and suggestions - i find the db design bit the easiest, it's the formatting the results how the client wants it that's so hard!