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.
