I am redesigning a database that had some serious problems. I've resolved
most of the issues except one: retrieving previous quotes.
The program that uses this database is a pricing program for a
custom-configured item. Thus, the cost of a particular end item is based on
a variety of other items and also on a variety of what I call "pricing
methods", i.e., by the sq. ft, by the unit, etc.. Cost is further based on
such factors as a manufacturer's multiplier, markup, and sales tax.
Over time, these items that make up the cost of this custom-configured item
may obviously change, sales tax may go up, the manufacturer may want more,
the labor rate may change, and so forth. Further, the pricing method may
change.
Yet we may often retrieve previous quotes. Those quotes may be from
yesterday, or from 2 years ago. So we will need to know what the calculated
values WERE, not what they would be if recalculated today.
It's simplistic enough to pass the caveat "don't store calculated values".
But this is simply unacceptable dogma in this situation -- we WILL save
calculated values. The question becomes "how".
You will notice in the table below some of the cost items, there are
actually another 30 or so discrete items that make up each product. Most of
those 30 or so items have a "pricing method" in the lookup table that will
determine how the item gets priced (example in the 2nd table shown).
My main concern is how to save the pricing method for EACH of these costs in
the JobCosts table. It's clearly not in 3N form to have a
"ColorCostPricingMethod" field, a "HardwareCostPricingMethod" field, and so
forth. Any advice on fixing this structure or a pointer to a relevant book
or online resource would be helpful (I have Louis Davidson's excellent book,
but it is not precisely on point).
CREATE TABLE [dbo].[JobCosts] (
[CostID] [int] NOT NULL ,
[MeasureID] [int] NULL ,
[LastRevisedDate] [smalldatetime] NULL ,
[MfrMultiUsed] [float] NULL ,
[TaxPercentUsed] [float] NULL ,
[SalesTaxUsed] [float] NULL ,
[OurMarkupUsed] [float] NULL ,
[ModelCost] [float] NULL ,
[ColorCost] [float] NULL ,
[HardwareCost] [int] NULL ,
[LaborCost] [int] NULL ,
[BaseItemCost] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[HardwareFinishCosts] (
[ItemListID] [int] NULL ,
[HardwareFinishID] [int] NULL ,
[MaximumWidth] [int] NULL ,
[Cost] [float] NULL ,
[PricingMethod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GOsame objects with different attributes are different entities.
object1 with price 20 is different from
object1 with price 30
therefore each object must have different primary keys
it will look like a fact table in the Warehouse database
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Earl" wrote:
> I am redesigning a database that had some serious problems. I've resolved
> most of the issues except one: retrieving previous quotes.
> The program that uses this database is a pricing program for a
> custom-configured item. Thus, the cost of a particular end item is based o
n
> a variety of other items and also on a variety of what I call "pricing
> methods", i.e., by the sq. ft, by the unit, etc.. Cost is further based on
> such factors as a manufacturer's multiplier, markup, and sales tax.
> Over time, these items that make up the cost of this custom-configured ite
m
> may obviously change, sales tax may go up, the manufacturer may want more,
> the labor rate may change, and so forth. Further, the pricing method may
> change.
> Yet we may often retrieve previous quotes. Those quotes may be from
> yesterday, or from 2 years ago. So we will need to know what the calculate
d
> values WERE, not what they would be if recalculated today.
> It's simplistic enough to pass the caveat "don't store calculated values".
> But this is simply unacceptable dogma in this situation -- we WILL save
> calculated values. The question becomes "how".
> You will notice in the table below some of the cost items, there are
> actually another 30 or so discrete items that make up each product. Most o
f
> those 30 or so items have a "pricing method" in the lookup table that will
> determine how the item gets priced (example in the 2nd table shown).
> My main concern is how to save the pricing method for EACH of these costs
in
> the JobCosts table. It's clearly not in 3N form to have a
> "ColorCostPricingMethod" field, a "HardwareCostPricingMethod" field, and s
o
> forth. Any advice on fixing this structure or a pointer to a relevant book
> or online resource would be helpful (I have Louis Davidson's excellent boo
k,
> but it is not precisely on point).
> CREATE TABLE [dbo].[JobCosts] (
> [CostID] [int] NOT NULL ,
> [MeasureID] [int] NULL ,
> [LastRevisedDate] [smalldatetime] NULL ,
> [MfrMultiUsed] [float] NULL ,
> [TaxPercentUsed] [float] NULL ,
> [SalesTaxUsed] [float] NULL ,
> [OurMarkupUsed] [float] NULL ,
> [ModelCost] [float] NULL ,
> [ColorCost] [float] NULL ,
> [HardwareCost] [int] NULL ,
> [LaborCost] [int] NULL ,
> [BaseItemCost] [float] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[HardwareFinishCosts] (
> [ItemListID] [int] NULL ,
> [HardwareFinishID] [int] NULL ,
> [MaximumWidth] [int] NULL ,
> [Cost] [float] NULL ,
> [PricingMethod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
>
>|||> same objects with different attributes are different entities.
> object1 with price 20 is different from
> object1 with price 30
> therefore each object must have different primary keys
> it will look like a fact table in the Warehouse database
I'd also suggest giving each entity a time context, an
ActiveSince/ActiveUntil datetime range. Consider whether these ranges should
be allowed to overlap. Would it make sense to allow "object1 with price 20"
and "object1 with price 30" be active at the same time? (Depends on the
actual requirements.)
ML
