Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2003-09-29 : 15:15:00
|
| Hi,I'm storing information for realestate and my table structure is like this:tblPropertypropertyID, propertyType ( such as houses, condos, business, land etc..)Now I will have tables for each type of property like tblHouses, tblCondoes etc since each property type will have its corresponding fields.Now when I do a select out of tblProperty, how can I inner join with the correct table since it is not known until the propertyType is read?Another approach would be to create a generic propertytype table, the problem with this is that some fields will be NULL since each propertytype is different.How to approach this problem since its prob. very common!TIA |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-29 : 15:26:46
|
| I'd say 1 Table...why don't you post your DDL of your tables as you have them defined now...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-09-29 : 15:52:49
|
| Can you do seperate stored procs for each property type, then call the appropriate one based on the PropertyType selection?-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-09-29 : 19:38:47
|
| I am planning on doing the following:Get me all the properties owned by a user. So if there are mulitple tables with varying fields, I can very well do a select with UNIONS (since the fields are different) and I can't do a inner join since I don't know which table to join since it dependson the propertyID!X002548: I don't have a DDL as of yet, I'm still trying to figure that part out.This should be a common problem, what is the industry standard approach? |
|
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2003-09-30 : 01:11:55
|
| I think u can put all properties in one table which may contains columns like propertyType(like Houses,condos....),propertyName,propertyValue,or u can split this table to two,which store String properties and Number properties separately.By do ing this,u can query via property Type Name and value.Most of all,u can dynamicly modify property type and property name.========================look! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-30 : 07:55:39
|
| Create 1 table of Properties. PK is propertyID or something like that. Each property has a type: "House","Condo", etc.Store in "Properties" ALL properties with their type, and ALL information that needs to be stored for everything regardless of what propertyType it is.From there, you create a subtable for each property type: PropertyHouses, PropertyCondos, etc.To query the whole thing, you use LEFT OUTER JOINS from the Properties table to each subtable.(I can post more details on this later, including a trick to have SQL ensure all data integrity in this scenerio using a calculated column (!) )Main concern: as your data changes, you need to create new "subtables" and alter the main view LEFT JOIN'ing to all subtables, thereby changing database objects. This is usually a no-no in that you want the objects to be stable regardless waht data you are storing. (i.e., you don't want to create 1 table per customer, thingss like that)HOWEVER -- a data change (new property type) will require lots of changing in both the front end of the app and the back end (new types of calculations, new info to store, new screens, new reports, etc) so I feel this is perfectly acceptable and in fact necessary.- Jeff |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-30 : 20:04:52
|
| What about going with more of a metadata approach where you have one table with the fields that will relate to every property such astblProperty: PropertyID, AddressStuff (multiple fields), Price, etc.And then a second table with the variable attributes/featurestblFeature: FeatureID, FeatureDescriptionAnd then the key, and junction table (for the many-to-many relationship) liketblPropertyXFeature: PropertyID, FeatureID, MoreDetails------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
|
|
|
|
|
|
|
|
design help please