Post reply

design help please

  • September 29, 2003 at 5:42 pm

    #82274

    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

    #476293

    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

    #476300

    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

    #476308

    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

    #476337

    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

    #476350

    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

    #476379

    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

    #476459

    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

    #476477

    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

    #476643

    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