|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Cascading stock values?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 have 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 -- Show quoteWayne 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 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 have > many years experience of TSQL, I have not used triggers before. > > > Thanks, Mike. 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. Show quote "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 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 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 have > > 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 correctly.
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/ "ML" <M*@discussions.microsoft.com> wrote in message So essentially, "proper normalization" makes you invincible. :-)news:DDECA756-D376-4F0D-845B-1C68D229A344@microsoft.com... > ...With proper normalization nothing can stop you. Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof. ML --- http://milambda.blogspot.com/ Unfortunately it's a system that I inhereted, but surely you would still have
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 have 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. Show quote "ML" wrote: > You'd be in a lot less trouble if you had normalized the data model correctly. > > 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/ 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 product. Is this correct? Maybe you should post some representative data, so that we can understand the issue correctly. 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. Show quote "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 product. > > Is this correct? Maybe you should post some representative data, so that we > can understand the issue correctly. > > > ML > > --- > http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics