Property Database Design Issue

Hi !!

I need some inputs for designing Property Database

Property Types:

Condo/Townhouse

Single Family Home

Multi Family Home

Rental

Property Features

Sq Ft

# Of floors

New Construction

Carpeted Floors

Hardwood Floors

Living Room

Formal Dining Room

# of Bedrooms

# of Bathrooms

Den/Office

Water : City or Private

Sewer: City or Septic

Family Roomand more

Lot Features

Mountain View

Golf Course Lot

Golf Course Viewand More

Community Features

Boating

Golf

Gym

Swimmingand More

Here is what I am thinking:

1) I probably don't need any table for Property Type as I can hard code them into Drop Down List

2) Water and Sewer feature can de coded into Drop Down List too

3) How rest of the table design should work?

Table called Property, Lot Feature, Community Feature, Property_Lot, Property_Community

Property Table has PropertyID (P1, P2, P3) and other Info

Lot Feature has Lot Feature ID (L1, L2, L3) and

Community Feature has (C1, C2, C3)

Property can have zero or More Lot Feature

soProperty_Lot table will have:

P1, L1

P1, L2

P1, L3

P2, L3

P3, L1

Property can have zero or more Community Feature, soProperty_Community

P1, C1

P2, C1

P2, C2

P3, C3

Is this right?

How do we design search query?

I think query will be dynamic query? correct?

Thanks !!!!

1) If you making them a radio button, ok. If you are putting it into a dropdown, I'd make a table entry.

2) See #1.

3) Looks pretty good, but when you say P1,P2,L1,L2, just make the ID's an autoincrementing identity, not 'P1' perse, but an identity of 1,2,3,etc. I'll assume that is what you meant.

Yes, that is right.

Seach for what?

I wouldn't use a dynamic query, no.

|||

Looked into your suggestions.

Search for:

Give me a list of property where Property Has feature L1 AND L2 and C1

or

Give me a list of property where Property has feature L1

Now, this type of query will need a join with other tables and parameters are completely unknown, # of parameters selected and passed to query are unknown till runtime. In this case what is the alternative for dynamic query?

--------------------

We also thought about something like this:

1) Property Table

2)Property_Lot table. This table looks like this:

0 = No

1 = Yes

Columns are:

PropertyID, MountainView, Golf View, Cornor Lot, River front Lot (and about 10 more)

Data:

P1, 0, 0, 1, 1

P2, 1, 1, 1, 0

This means Property P1 is Cornor Lot and River front

P2 is Mountain View, Golf View, Cornor Lot

Now, the problem is how to perform query on this? We might have to pass all parameter in particular order?

---- OR -----

Should I create column in Property Table "Lot Info", "Community Info"

and save comma seperated value in that column

PropertyID Lot Features Community Features

P1 L1, L2, L3 C1, C3

P2 L3 C2, C3

and now use SQL Server Full Text Search when some one says "Give me a list on Properties with Lot Feature L3 and Community Feature C3"

or "Give me a list of Properties with Lot Feature L1"

Any suggestion?

|||

You first design is best (The one in the original message).

Yes, you would do a join. Sorry, you are probably best just doing a dynamic query. You can do a static one, but it'd be extremely large, and probably pretty inefficient.

A simple dynamic query using your original design would be like:

SELECT *
FROM Properties p
JOIN PropertyLot pl ON (p.PropertyId=pl.PropertyId AND pl.LotFeatureID={Feature 1})
JOIN PropertyLot pl2 ON (p.PropertyId=pl2.PropertyId AND pl2.LotFeatureID={Feature 2})

That is assuming that you have the feature id's already, if you don't then you can do this:

SELECT *
FROM Properties p
JOIN PropertyLot pl ON (p.PropertyId=pl.PropertyId)
JOIN LotFeatures lf1 ON (pl.LotFeatureID=lf1.LotFeatureID AND lf1.Description={Feature 1})
JOIN PropertyLot pl2 ON (p.PropertyId=pl2.PropertyId)
JOIN LotFeatures lf2 ON (pl.LotFeatureID=lf2.LotFeatureID AND lf2.Description={Feature 2})

etc.

You can do a static one like this:

SELECT *
FROM Properties p
WHERE (SELECT COUNT(*) FROM PropertyLot pl JOIN Split(@.LotFeatures) s ON (pl.LotFeatureID=s.ID AND p.id=pl.id))=ValueCount(@.LotFeatures)

That assumes that you have a comma-delimited list of LotFeatureID's in @.LotFeatures, and that you have already written a Split function that takes a comma-delimited varchar field and returns a table with a single column named ID that has each value in a new row. ValueCount is a function (You have to write it) that accepts a comma-delimited varchar and returns the number of values in it. I assume that a null varchar is 0, and an empty string is 0 for count, and both return no rows from split.

|||How about using Full Text Search on 2nd approach i.e. Comma Seperated list for LotFeatures and CommunityFeatures?