normalised design causing problems displaying results

Hi

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!

Many thanks

EmilyB

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

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

Thanks

Emily

right i found the forum topic.

yikes.

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.

Thanks anyway

Emily

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: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

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

helps?

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.

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

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

erm yes indeed!

i'm stumped

change the user's expectations

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

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..."

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.

Adam