September 29, 2003 at 5:42 pm
Hi,
I'm storing information for realestate and my table structure is like this:
tblProperty
propertyID, 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
September 29, 2003 at 7:42 pm
What is the structure of tblHouses, tblCondoes etc...
Are they that different that you need seperate fields?
Can you post the complete schema for these tables?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
September 30, 2003 at 1:30 am
I suppose it also depends on what do you wish to select "out of tblProperty", and what do you want to do with selected data... if the question stands simply like "I have one propertyID and want to select a row from corresponding table", then it could be managed via sp_executesql (this is simplified and will generate an error if you enter other ID than that of a house):
declare @sql nvarchar(500)
declare @tblname nvarchar(50)
declare @ID int
set @ID = (...enter your propertyID here...)
set @tblname = CASE
WHEN (select propertyType from tblProperty where propertyID = @ID) = 'house' THEN 'tblHouses'
ELSE 'ERROR' END
select @sql = 'SELECT * FROM '+ @tblname + ' WHERE propertyID = ' + CONVERT(nvarchar(10),@ID)
execute sp_executesql @sql
You just have to find a way how to pass the correct propertyID to the @ID parameter, and it will work. You can avoid the CASE statement and replace it with a simple select, if you create a table which will store all values for the field "propertyType" and a corresponding table name.
Well, that's how it could be done... but I'm not really sure whether it is practical. Maybe if you tell us a bit more about the structure, and what do you want to use the selected data for..?
September 30, 2003 at 2:03 am
I would go for a general 'property' table that stores all common data (like address, price, sq ft, ...). No problem if some of the fields are null (you probably won't get all data from each house anyway).
Next, I would add a reference to a specific table for each type of property. I would think that in general, you would only need this specific stuff when getting the details of a single property.
So in that case, there is no need to do any complex / general queries. You can easily construct a query for each property type.
September 30, 2003 at 5:01 am
I'm in the beginning stage of the design so i'm not really sure HOW different the tables will be.
In general, I feel this is a common problem so I just thougth I'd ask here! Thanks!
The only problem I have with going with a single table is that eventually the design expands, and thus the single table approach really gets bloated as the feature set gets more complex etc.
I just wish there was another way withouth doing multiple querys from the database to the application just to get the rows.
BTW, I would be selecting all rows in tblProperty that belong to a single owner, which means the owner can own houses, condoes, cars etc whatever, so its multiple results set back so if its in multiple tables it would mean seperate queries PER propertyTYpe since its in different tables.
TIA
September 30, 2003 at 6:12 am
I guess I'm an iconoclast here, but, if these types of property are fundamentally different (as land and cars certainly are), I would not have a "tblProperty" at all, but instead separate tables for each different type of property. Any attributes that can be directly compared (description, monetary value?) could be included in a coalescing query joining on the Owner ID when you need consolidated reporting. If you need a unique ID for each property, then use a LastIDs table with any inserts.
CREATE PROC dbo.NextPropertyID
@NextID int OUTPUT AS
SET NOCOUNT ON
UPDATE dbo.LastIDs
SET @NextID = LastPropertyID + 1
--Jonathan
--Jonathan
September 30, 2003 at 8:41 am
sql777,
since you wish to retrieve entirely different data from each of the tables (say, square meters for land, speed for cars and number of tenants for a house) - and do it at once, I fail to see how this could be done by a single query - or, how a single query would help. You still need to arrange the data so that the user can understand it.
I agree with Jonathan that separate tables seem to be better solution for your particular needs, and it is still possible to maintain unique ID for each property. Another possibility would be to store basic set of common values (type, name, date of purchase, value, owner etc.) in the tblProperty and the rest of values in type-specific tables. Then you would need table specific queries only there where you need to retreive the type-specific info. If in most cases the common info is all you want, then it should work fine. If not, then I would prefer entirely separate tables w/o tblProperty, as Jonathan proposed.
September 30, 2003 at 5:16 pm
I think there is a need for you to forget about what table structure you use and analyse your data a bit more and organise things at the logical level. After that exercise you might find some consistencies in the data that allow you to put it together, or keep it apart.
That said Jonathan's suggestion would probably fit your needs. As for retrieving the data, that's a different thing altogether. What interface will the end-user be displaying the data in? Will ADO be used to retrieve the data? If so, you can take advantage of it's multiple resultset capability to display your data. Then you can have seperate queries in one stored procedure and providing all the information at once.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 09/30/2003 5:19:59 PM
--------------------
Colt 45 - the original point and click interface
September 30, 2003 at 11:55 pm
This is an option that you could look at. Create the master table "Properties" with the comon attirbutes of all property types. Then create n number of specialized tables like "Vehicles", "Buildiings" etc. Now for your querying purposes create a denormalized view that would have all the attributes of the different child tables. The view definition would be union of all records in the child tables. This is an option that you could look at.
Thanks
October 1, 2003 at 3:35 pm
Another option in a slightly different approach is that you could easily put your objects in different tables as was suggested above. But when you want to return the results use FOR XML as the output and a style sheet to display the data. Since each object you are talking about has different types of data I would go this route myself.
Just a thought! 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Edited by - gljjr on 10/01/2003 3:36:03 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply