Cascading stock values?

Hi,
I have a database containing products, the tables of which are basically as
follows:
table_PRODUCTS
-->(products may or may not have colours)
table_PRODUCT_Colours
-->(colours may or may not have sizes)
table_PRODUCT_Colour_Sizes
- The tables currently hold stock values at all 3 levels, and the sum of
stock at Colour_Sizes level for each product must equal the sum of the stock
at Colours level, which must also equal the stock level held at the main
Product level.
- Some Products may not have Colour_Size records, and some may not have
Colours either.
I am trying to find out what is the best way to keep the 3 levels of stock
consistent, but I think the best way to do it would be:
- Using Triggers, and
- Always only allow stock to be adjusted at the highest level for a
particular product, i.e. Check for higher levels, and if found don't allow
updates to the level in question
- If an UPDATE, DELETE or INSERT operation occurs at the highest level, then
use the trigger to adjust the stock at the lower level automatically.
Please can you tell me whether this is the best way to do it, and if so any
pointers about how I would go about setting the triggers up - Although I hav
e
many years experience of TSQL, I have not used triggers before.
Thanks, Mike.You probably already know that these aggregate values violate database
normalization, and that problems you are talking about come from that
violation..
If these values are not used frequently OR there are few rows... I would NOT
stored the aggregates . Instead provide views over the tables which provide
the aggregate values..
If you MUST denormalize AND the upper level must always be the sum of the
lower levels, then there is a consistency problem I do not understand...If
the parent is always the sum of the children, but there may not be children
rows, then is the parent value 0 ( or null)...? If you allow the values to
be adjusted ONLY at the highest level, how does that allocate to the lower
levels?...
It seems to me you would ONLY allow changes at the lowest level, and the
higher levels would be calculated...
In the end if you mus do this... you might need to post more details ( with
some sample rows)...
By the way, it IS normal to maintain denormalized fields via Triggers... So
you are on the right technology track..
Good luck
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Owen" wrote:

> Hi,
> I have a database containing products, the tables of which are basically a
s
> follows:
> table_PRODUCTS
> -->(products may or may not have colours)
> table_PRODUCT_Colours
> -->(colours may or may not have sizes)
> table_PRODUCT_Colour_Sizes
>
> - The tables currently hold stock values at all 3 levels, and the sum of
> stock at Colour_Sizes level for each product must equal the sum of the sto
ck
> at Colours level, which must also equal the stock level held at the main
> Product level.
> - Some Products may not have Colour_Size records, and some may not have
> Colours either.
> I am trying to find out what is the best way to keep the 3 levels of stock
> consistent, but I think the best way to do it would be:
> - Using Triggers, and
> - Always only allow stock to be adjusted at the highest level for a
> particular product, i.e. Check for higher levels, and if found don't allow
> updates to the level in question
> - If an UPDATE, DELETE or INSERT operation occurs at the highest level, th
en
> use the trigger to adjust the stock at the lower level automatically.
> Please can you tell me whether this is the best way to do it, and if so an
y
> pointers about how I would go about setting the triggers up - Although I h
ave
> many years experience of TSQL, I have not used triggers before.
>
> Thanks, Mike.|||You'd be in a lot less trouble if you had normalized the data model correctl
y.
As I see it:
1) Entities:
Products
2) Attributes:
Colour
Size
3) Relationships:
Products <-- Colour (one to zero or many)
Products <-- Size (one to zero or many)
With proper normalization nothing can stop you.
Consider changing the schema and just maybe the question you were trying to
ask will be answered as if by itself.
ML
http://milambda.blogspot.com/|||Thanks for the quick response Wayne.
Yes, you are right it is not necessarily a good / normalised design.
In answer to your 3rd paragraph " If you MUST denormalize AND ...", it
simply comes down to the fact that all products have the highest
(table_PRODUCT) level record, e.g. A toaster, some products also have the
second level, e.g. A car (blue, red, green etc), and a few have all 3 levels
,
e.g. A pair of trousers (blue, green, red) in various sizes (blue 32" waist,
blue 34" waist etc), so not all parent records have children.
It seems as though from your comment I was thinking along the right lines.
So it seems that as you have partly suggested I would probably need the
following triggers/rules:
- Child stock can always be updated, but when it is always update the parent
stock by adding up all of the childs peers stock
- If a parent has any children, don't let the stock be updated apart from by
a trigger from a child stock change.
Would you think this covers it?, and if so what would the triggers roughly
look like?
Thanks, Mike.
"Wayne Snyder" wrote:
> You probably already know that these aggregate values violate database
> normalization, and that problems you are talking about come from that
> violation..
> If these values are not used frequently OR there are few rows... I would N
OT
> stored the aggregates . Instead provide views over the tables which provid
e
> the aggregate values..
> If you MUST denormalize AND the upper level must always be the sum of the
> lower levels, then there is a consistency problem I do not understand...If
> the parent is always the sum of the children, but there may not be childre
n
> rows, then is the parent value 0 ( or null)...? If you allow the values t
o
> be adjusted ONLY at the highest level, how does that allocate to the lower
> levels?...
> It seems to me you would ONLY allow changes at the lowest level, and the
> higher levels would be calculated...
> In the end if you mus do this... you might need to post more details ( wit
h
> some sample rows)...
> By the way, it IS normal to maintain denormalized fields via Triggers...
So
> you are on the right technology track..
> Good luck
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Mike Owen" wrote:
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:DDECA756-D376-4F0D-845B-1C68D229A344@.microsoft.com...
> ...With proper normalization nothing can stop you.
So essentially, "proper normalization" makes you invincible. :-)|||Unfortunately it's a system that I inhereted, but surely you would still hav
e
the same problem even if you did it as you indicated below, e.g.
Keeping it simple you might have a situation where a particular product has
no colours, very simple you would simply update the stock directly against
it, but another product may have colours, in which case you would either hav
e
to:
- ignore the stock at product record level altogether, or
- use triggers at the colour level to keep the stock value at product level
up to date
If you chose the first option you would then have to write application level
code for anything that looks at product level stock in this case, so it is
either not seen, or is swapped for colour level stock.
Cheers, Mike.
"ML" wrote:

> You'd be in a lot less trouble if you had normalized the data model correc
tly.
> As I see it:
> 1) Entities:
> Products
> 2) Attributes:
> Colour
> Size
> 3) Relationships:
> Products <-- Colour (one to zero or many)
> Products <-- Size (one to zero or many)
> With proper normalization nothing can stop you.
> Consider changing the schema and just maybe the question you were trying t
o
> ask will be answered as if by itself.
>
> ML
> --
> http://milambda.blogspot.com/|||Of course all combinations should be considered:
Product : Colour : Size
value null null
value value null
value value value
value null value
This way a specific combination of values represents an instance of a produc
t.
Is this correct? Maybe you should post some representative data, so that we
can understand the issue correctly.
ML
http://milambda.blogspot.com/|||Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof.
ML
http://milambda.blogspot.com/|||Thanks for all of your help and support.
Following this I had a go at doing my first set of triggers, and they all
seem to work fine how ever many levels I have got.
Cheers, Mike.
"ML" wrote:

> Of course all combinations should be considered:
> Product : Colour : Size
> value null null
> value value null
> value value value
> value null value
> This way a specific combination of values represents an instance of a prod
uct.
> Is this correct? Maybe you should post some representative data, so that w
e
> can understand the issue correctly.
>
> ML
> --
> http://milambda.blogspot.com/