Database design and functions - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Database design and functions

Author

Topic

akiles
Starting Member

8 Posts

Posted - 2011-05-18 : 16:30:07

Hi,

First, I would like to apologize for my ignorance -I'm new to sql.
Second, I'd like to apologize for the length of this post.

I am trying to get around a design problem with a database I am designing. The database stores real estate related information. Specifically building descriptions. For instance, house 1 has attributes like area, type of house, number of bathrooms, exterior walls, etc. This information is later used to assign a value to the property. There is also a specific valuation model i have to follow.

For instance, a 2-story home with 2000sqft, brick walls, 2 full baths and 1 half bath would be valued as follows:
value = [area] * [sqft rate for a 2-story] + [perimeter] *[rate for exterior walls] + [full baths] *[rate for full baths] + [half baths] *[rate for half baths]

Here's my problem:
The attributes are stored in one table (the property table). The costs are stored in another (the cost table). The costs table would look like this:

attribute type cost
brick walls walls 1.5
wood walls walls 0.74
full baths f.baths 2000
half baths h.baths 1300
1 story home 40
2 story home 23.5
split foyer home 45.2

So there is no way (currently) that I could retrieve 1 record from the property table and match it with one single record from the cost table. I came around this problem by creating a function. I have a query that uses all the information from the property table and retrieves the cost of each attribute. Then I can calculate the cost component of each attribute and add them together to obtain the total cost of the building.

The thing is, the function is a query itself that returns values from the cost table. Each property has many attributes (about 20) So if I want to calculate the value for one building, I'm actually running 20 queries. The issue is I have almost 200,000 buildings. I did atest with 50,000 records and it took 46mins.

I've been messing with this for weeks, and I can't find a way aroudn using a function with the current table configuration. So I guess that I should try to redesign the configuration of the tables, but I can't think of a different solution. Could someone please point me in the right direction? Maybe a sample database with a similar task?

I appreciate any ideas.

Once again, thanks and I apologize for the lengthy question.

Regards

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 16:37:49

Can you post the structure of the Property table, the code for the function you're using, and some sample data with the expected results for the value you're calculating?

akiles
Starting Member

8 Posts

Posted - 2011-05-19 : 09:32:52

Thanks for your response, Rob.

Here are the two tables involved (the Property table, called residential, and the costs table).
cYear Account Style Foundation ExteriorWalls FullBaths HalfBaths
----------- ---------- ----- ---------- ------------- --------- ---------
2010 000001 01 5 21 2 0
2010 000002 01 0 00 1 1
2010 000003 03 0 00 1 0
2010 000004 01 0 21 1 1
2010 000005 05 6 00 2 0
***This table contains several more columns, but for the sake of space... Let's assume there is an extra column called Area and every home is 1000sqft.

cYEAR CostGroup CostID CostDesc Cost
----------- ----------- ------ -------------- ---------
2010 BATHS FB Full Bathroom 3000.0000
2010 STYLE 01 1 Story 76.16
2010 STYLE 02 1 1/2 Story 69.71
2010 STYLE 03 2 Story 60.13
2010 EXTW 00 A/V 1.25
2010 EXTW 21 Brick 15.13

Here is the funtion (scripted as "create") to retrieve the costs:

USE [RealEstate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetCost] (@cYear int, @CostGroup nvarchar(20), @CostID nvarchar(6))
RETURNS numeric (10,4)
WITH EXECUTE AS CALLER
AS
BEGIN
     declare @itemCost numeric(10,4)
		select @itemCost = Cost from dbo.Costs 
		where (cYEAR=@cYear and CostGroup=@CostGroup and CostID=@CostID)
     RETURN(isnull(@itemCost,0))
END
GO

So that's my design -and it's obviously flawed.

The valuation model is:
[style] * [area] + [area]*[ext wall rate] + [fullbaths]*[full bath rate] + [halfbaths]*[half bath rate]

Take account 00001 as an example. The cost per sqft of a 1 story house is 76.16 times the area (1000)= $76,160
Then the house has exterior brick walls, which have a cost of 15.13 times the area = $15,130.
Finally, there are 2 full baths, for a total of $6,000

So, to extract the cost of the exterior walls:

select dbo.fn_GetCost(2010,'extw','21')

To get the value of the whole thing:

use RealEstate

select cyear, account, 
	dbo.fn_GetCost(cyear,'style',Style)*[area] as StyleCost, 
	dbo.fn_GetCost(cyear,'extw',ExteriorWalls)*[area] as ExtWallCost,
	dbo.fn_GetCost(cyear,'BATHS','fb')*[FullBaths] as FBCost,
	dbo.fn_GetCost(cyear,'BATHS','hb')*[HalfBaths] as HBCost
from Residential
where cyear=2010

I hope I posted this in the appropriate manner. If not, I apologize.
Thanks once again for your time.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 10:04:57

See if this is faster:

SELECT Home.cYear, Home.Account, IsNull(Style.Cost,0) * Home.Area StyleCost, 
IsNull(Exterior.Cost,0) * Home.Area ExtWallCost,
IsNull(FullBaths.Cost,0) * Home.FullBaths FBCost,
IsNull(HalfBaths.Cost,0) * Home.HalfBaths HBCost
FROM Residential Home
LEFT JOIN Costs Style ON Home.Style=Style.CostID AND Home.cYear=Style.cYear AND Style.CostGroup='STYLE'
LEFT JOIN Costs Exterior ON Home.ExteriorWalls=Exterior.CostID AND Home.cYear=Exterior.cYear AND Exterior.CostGroup='EXTW'
LEFT JOIN Costs FullBaths ON FullBaths.CostGroup='BATHS' AND Home.cYear=FullBaths.cYear AND FullBaths.CostID='FB'
LEFT JOIN Costs HalfBaths ON HalfBaths.CostGroup='BATHS' AND Home.cYear=HalfBaths.cYear AND HalfBaths.CostID='HB'

akiles
Starting Member

8 Posts

Posted - 2011-05-20 : 11:30:47

Rob,
Thank you so much for taking the time to help me with this.
The query I was using, with the function to calculate the value, took approx. 46mins to process 60,000.
I impplemented your solution, and although I did not use all the parameters (like fireplaces, typle of flooring, etc), the time was reduced to 42 seconds -yes, SECONDS!

I still have a question:
When I did the joins, I had to bring out the cost table again aliased for each join. Here is a sample -is that what you meant?

SELECT
Residential.cYear,
Residential.Account,
Residential.ExteriorWalls,
Costs.CostGroup,
Costs.CostID,
Costs.CostDesc,
Costs.Cost,
Residential.Heating,
theating.CostDesc as dHeating,
tHeating.Cost as cHeating

FROM Costs AS tHeating
RIGHT OUTER JOIN Residential ON
tHeating.CostID = Residential.Heating AND
tHeating.cYEAR = Residential.cYear AND
tHeating.CostGroup = 'HEATING'
LEFT OUTER JOIN Costs ON
Residential.cYear = Costs.cYEAR AND
Residential.ExteriorWalls = Costs.CostID
AND Costs.CostGroup = 'EXTWALLS'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 11:39:11

Yes, for each attribute (style, walls, heating, baths, etc.) you'll need a separate join to the Costs table on the appropriate Residential column, with the added condition for CostGroup. Otherwise you might get duplicate or invalid data.

akiles
Starting Member

8 Posts

Posted - 2011-05-25 : 13:20:44

Hey, Rob.
Thanks for your help man: 59,020 records processedin 4 seconds!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-25 : 13:30:46

You're welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources