Hi I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2 e.g Sales tax = 10% Use Tax = 10% Share1 = 60% Share2 = 40% So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax So Use tax Amt (B) = cost1 * share2 * Use tax same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on.. and total cost with Use tax = Cost1 +B, Cost 2 +B etc. So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size. Some of these fields might be used for making reports. I was wondering which is a better approach out of the below 4: 1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or 2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier. 3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.
Your help is greatly appreciated.
Thanks | | Indira M Thursday, January 24, 2008 11:30 PM | Will the tax values change later on in a manner that affects the historical data if you are using computed columns and you change the computation? If so, then you are probably best off storing the data in permanent columns, which is nasty, but sometimes unavoidable. You could add a datetime column and use it in the computation to pick the appropriate % for that date I guess as well. That is a tough call. If the values are just for reporting, I would likely do it in a view, since it can be generated on the fly that way. | | Jonathan Kehayias Friday, January 25, 2008 4:55 AM | Thanks a lot for quick and detailed reply.
Tax values can change, but always from User Interface, and computation remains the same. If I make a change directly in tax values in database, wouldn't the computation on formula columns re-calculate the values?
And this table needs to be referred in both User Interface (I show the total columns on UI too where users might change the Cost values/tax values, and I need to refresh the total columns) and Reporting. In that case, which approach could be better?
| | Indira M Friday, January 25, 2008 7:07 PM | Look at the below, and what it does. This would be one method of extending the fucntionality without extending that table.
Code Snippet
create table dbo.salesdata
(
SalesID int identity primary key,
customerid int,
ItemDesc varchar(100),
ItemCost decimal(18,2),
ItemShare1 decimal(18,2),
ItemShare2 decimal(18,2),
PurchaseDate datetime
)
create table dbo.TaxValues
(
ValueId int identity primary key,
taxtypeid int,
valueamt decimal(18,2),
effbegindate datetime,
effenddate datetime
)
create table dbo.TaxTypes
(
TaxTypeId int identity primary key,
TaxType varchar(25)
)
create function dbo.GetTaxValue
(
@TaxTypeID int,
@SalesDate datetime
)
Returns decimal(18,2)
AS
BEGIN
DECLARE @RetVal decimal(18,4)
SELECT @RetVal = valueamt
FROM TaxValues
WHERE TaxTypeID = @TaxTypeID
AND @SalesDate Between EffBeginDate and EffEndDate
Return (@retval)
END
Insert into TaxTypes
SELECT 'UseTax'
UNION SELECT 'SalesTax'
INSERT INTO TaxValues
SELECT 1, .02, '10/01/2007', '11/30/2007'
union SELECT 2, .06, '10/01/2007', '11/30/2007'
union SELECT 1, .02, '12/01/2007', '01/31/2008'
union SELECT 2, .08, '12/01/2007', '01/31/2008'
INSERT INTO SalesData
SELECT 1234, 'SQL Server Books Online', 99.99, 0.60, 0.40, '11/05/2007'
UNION SELECT 1234, 'SQL Server Books Online', 99.99, 0.50, 0.50, '12/03/2007'
UNION SELECT 1234, 'SQL Server Books Online', 99.99, 0.20, 0.80, '12/15/2007'
create view vw_salesdata_withtaxamounts
as
select salesid, customerid, itemdesc, itemcost,
itemshare1 , itemshare2, purchasedate,
dbo .GetTaxValue(1, purchasedate) as UseTaxAmt,
dbo .GetTaxValue(2, purchasedate) as SalesTaxAmt
FROM dbo.salesdata
create view vw_salesdata_extended
as
select salesid, customerid, itemdesc, itemcost,
itemshare1 , itemshare2, purchasedate,
itemcost * Itemshare1 * salestaxamt [SalesTax],
itemcost * Itemshare2 * UseTaxamt [UseTax],
itemcost + (itemcost * Itemshare1 * salestaxamt) + (itemcost * Itemshare2 * UseTaxamt) [TotalCost]
from vw_salesdata_withtaxamounts
| | Jonathan Kehayias Friday, January 25, 2008 8:29 PM | Thanks a lot for a detailed code snippet. That really helps. I might use the view option to calculate values on the fly and not store the values in table.
| Jonathan Kehayias wrote: |
|
Look at the below, and what it does. This would be one method of extending the fucntionality without extending that table.
Code Snippet
create table dbo.salesdata
(
SalesID int identity primary key,
customerid int,
ItemDesc varchar(100),
ItemCost decimal(18,2),
ItemShare1 decimal(18,2),
ItemShare2 decimal(18,2),
PurchaseDate datetime
)
create table dbo.TaxValues
(
ValueId int identity primary key,
taxtypeid int,
valueamt decimal(18,2),
effbegindate datetime,
effenddate datetime
)
create table dbo.TaxTypes
(
TaxTypeId int identity primary key,
TaxType varchar(25)
)
create function dbo.GetTaxValue
(
@TaxTypeID int,
@SalesDate datetime
)
Returns decimal(18,2)
AS
BEGIN
DECLARE @RetVal decimal(18,4)
SELECT @RetVal = valueamt
FROM TaxValues
WHERE TaxTypeID = @TaxTypeID
AND @SalesDate Between EffBeginDate and EffEndDate
Return(@retval)
END
Insert into TaxTypes
SELECT 'UseTax'
UNION SELECT 'SalesTax'
INSERT INTO TaxValues
SELECT 1, .02, '10/01/2007', '11/30/2007'
union SELECT 2, .06, '10/01/2007', '11/30/2007'
union SELECT 1, .02, '12/01/2007', '01/31/2008'
union SELECT 2, .08, '12/01/2007', '01/31/2008'
INSERT INTO SalesData
SELECT 1234, 'SQL Server Books Online', 99.99, 0.60, 0.40, '11/05/2007'
UNION SELECT 1234, 'SQL Server Books Online', 99.99, 0.50, 0.50, '12/03/2007'
UNION SELECT 1234, 'SQL Server Books Online', 99.99, 0.20, 0.80, '12/15/2007'
create view vw_salesdata_withtaxamounts
as
select salesid, customerid, itemdesc, itemcost,
itemshare1, itemshare2, purchasedate,
dbo.GetTaxValue(1, purchasedate) as UseTaxAmt,
dbo.GetTaxValue(2, purchasedate) as SalesTaxAmt
FROM dbo.salesdata
create view vw_salesdata_extended
as
select salesid, customerid, itemdesc, itemcost,
itemshare1, itemshare2, purchasedate,
itemcost * Itemshare1 * salestaxamt [SalesTax],
itemcost * Itemshare2 * UseTaxamt [UseTax],
itemcost + (itemcost * Itemshare1 * salestaxamt) + (itemcost * Itemshare2 * UseTaxamt) [TotalCost]
from vw_salesdata_withtaxamounts
| | | | Indira M Thursday, February 14, 2008 11:29 PM |
|
index > SQL Server Database Engine > Database table design for huge number of columns