locked
Input on Table Driven Design RRS feed

  • Question

  • Hello,

    I am beggining to refactor alot of sp's to be table driven and have come up with two ways to approach the problem. I would appreciate comments, criticism, or other ideas about accomplishing the end result.

    Here is the typical SP scenario:

    (...TSQL Query ) AS Cust
    
    SELECT * FROM view_ABC 
    WHERE CustSSN = @custssn
    AND Cust.PurchType in ('TSFA', 'NTSA', 'AUTS', .....)
    
    

    So of course the hard coded list has to constantly be changed and code pushed when we add / remove products. Instead it is my idea to make the sp table driven then throw a web GUI over the control table(s). From there the Business Analyst can add / remove as they see fit and we are doing code pushes every week etc.

    Table driven Method 1: This method seeks to join to a control table. Slick. If there isn't a match then by the very nature of a Join unmatched rows are dropped and Join are ....FAASSSTTT. On the flip side I can see myself ending up with ALOT of control tables for every nuance. For example one Sp get's dicontinued products while another gets active products. Well then that's two control tables. I know I could add an active flag to the ProductsControl table but I am trying to keep the example simple.

    Table driven Method 2: This method has larger more general control tables with rows. So a row may have the "list" of Active products. My query would then match to that rows list field. This *SEEMS* as though it may be slower but it appears that I would have fewer objects to manage.

    I'd say I am leaning towards the first method but the thought of numerous objects...doesn't scare me, just cautions me, to make sure this is the best way to go.

    Thank You for your input / constructive criticism.

    JB

    Monday, November 1, 2010 7:29 PM

Answers

  • Zzpluralza,

    I think the question is really how those tables (Core5ProdsControl, Least5ProdControl, etc) are populated? Is it the manual process or some sort of the calculations?

    The approach with one MasterControl and 1 row per group with column that represents the multiple values/codes is not really good. You will need to build dynamic SQL (or parse the list) in order to accomplish that. I would suggest to do something like:

    create table dbo.ControlGroups(
    	ControlGroupID smallint not null,
    	ControlGroupName varchar(32) not null,
    	
    	constraint PK_ControlGroups
    	primary key clustered(ControlGroupID)
    )
    go
    
    insert into dbo.ControlGroups values(1,'Best Selling 5') -- etc
    go
    
    create table dbo.GroupCodes
    (
    	ControlGroupId smallint not null,
    	Code char(4) not null,
    	
    	constraint PK_GroupCodes
    	primary key clustered(ControlGroupId, Code),
    	
    	constraint FK_GroupCodes_ControlGroups
    	foreign key(ControlGroupId)
    	references dbo.ControlGroups(ControlGroupId)
    )
    go
    
    -- and selects
    SELECT * 
    FROM view_ABC 
    WHERE 
    	CustSSN = @custssn and
    	Cust.PurchType in 
    	(
    		select Code 
    		from dbo.GroupCodes
    		where ControlGroupId = @ControlGroupId
    	)
    
    
    If the Codes have more attributes, you probably want to maintain another table for the Codes and use GroupCodes table as the references table for Many-to-Many relations.


    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, November 2, 2010 3:08 PM
  • Hi zzpluralza,

    I understand your point about discontinued etc. being an attribute of the products entity and so my simple example was thus defeated. Let me try it this way. We have a query that looks for 5 specific products out of say 5,000 products. So should I have a control table named says Core5ProdsControl and it would have 5 rows. 1 for each product and then the above mentionedAND Cust.PurchType in ('TSFA','NTSA', 'AUTS', .....) can be replaced with a join. By this logic I would then also have a table for Least5ProdsControl, and BestSell5ProdsControl, and MostReturnedProdsControl etc.

    These extra examples are also attributes of the Productss table. What
    products are core products is a management decision; this attribute
    should be modeled as a column in the product table. The top 5 best
    selling products is a very dynamic attribute, that usually will not be
    stored, but computed when needed. (Unless the computation is very
    expensive, the attribute is queried often, and the business can accept
    this attribute being out of date a bit at ties - in that case, you
    could consider computing the top 5 best sellers at given intervals and
    storing the result -again, in a column in the Products table.

    So as I understand it what you are suggesting is actually a third option....No control tables or unlikely as these examples could all be attributes of the products table. That could get interestring as we have to play ball with a dinosaur known as the AS400 and getting them to change is like telling them there's a meteor hurtling towards them. In other words anything we suggest they see as their possible extinction....which is another thread altogether.

    Yes, that is the idea. Though the AS400 and the associated red tape
    for getting changes through is new information. But not information
    that suddenly makes me change my mind.
    You obviously ARE able to create tables on SQL Server, without going
    through all the hoops associated with changing "the dinosaur". So
    instead of making one or more control tables, you can make a table
    "ProductsExtraAttributes" (or maybe even use a name that actaully IS
    appealing). The rows in this table are associated through a 1:1
    relationship (not enforced) to the Products table - preferably by
    using the same primary key that is also the key in the AS400 table.

    It should not be any extra work for the people who have to maintain
    these attributes - whether they add a product and remove another one
    to a list, or add a value in a row and remove it in another row, the
    amount of work remains the same.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, November 2, 2010 8:01 PM

All replies

  • Hi JB,

    I would definitely recommend the second method. Or rather a variation on it.

    Your description actually already gives it away. You mention that one query needs discontinued products, and another wants active products. But being active or discontinued is an attribute of the product entity type. Data about products should be stored in the products table. So the products table should have a column to store the active/discontinued status. Or maybe a nullable datetime column, "distontinued_on" - a value of NULL meaning it's not discontinued yet (and thus an active product).

    I'm willing to bet that as you research more stored procedures that use hard-coded selections, you'll find more forgotten attributes that you should just add to the tables.


    Hugo Kornelis
    SQL Server MVP

    Monday, November 1, 2010 10:44 PM
  • Hugo,

    I understand your point about discontinued etc. being an attribute of the products entity and so my simple example was thus defeated. Let me try it this way. We have a query that looks for 5 specific products out of say 5,000 products. So should I have a control table named says Core5ProdsControl and it would have 5 rows. 1 for each product and then the above mentioned AND Cust.PurchType in ('TSFA', 'NTSA', 'AUTS', .....) can be replaced with a join. By this logic I would then also have a table for Least5ProdsControl, and BestSell5ProdsControl, and MostReturnedProdsControl etc.

    Conversely I can have a table say MasterControl and it has 1 row and a field of that row has the core 5 another row that has Least5, another row that has BestSelling5 etc.

    So as I understand it what you are suggesting is actually a third option....No control tables or unlikely as these examples could all be attributes of the products table. That could get interestring as we have to play ball with a dinosaur known as the AS400 and getting them to change is like telling them there's a meteor hurtling towards them. In other words anything we suggest they see as their possible extinction....which is another thread altogether.

    So am I being dense or catching on to your point?

    JB

    Tuesday, November 2, 2010 2:51 PM
  • Zzpluralza,

    I think the question is really how those tables (Core5ProdsControl, Least5ProdControl, etc) are populated? Is it the manual process or some sort of the calculations?

    The approach with one MasterControl and 1 row per group with column that represents the multiple values/codes is not really good. You will need to build dynamic SQL (or parse the list) in order to accomplish that. I would suggest to do something like:

    create table dbo.ControlGroups(
    	ControlGroupID smallint not null,
    	ControlGroupName varchar(32) not null,
    	
    	constraint PK_ControlGroups
    	primary key clustered(ControlGroupID)
    )
    go
    
    insert into dbo.ControlGroups values(1,'Best Selling 5') -- etc
    go
    
    create table dbo.GroupCodes
    (
    	ControlGroupId smallint not null,
    	Code char(4) not null,
    	
    	constraint PK_GroupCodes
    	primary key clustered(ControlGroupId, Code),
    	
    	constraint FK_GroupCodes_ControlGroups
    	foreign key(ControlGroupId)
    	references dbo.ControlGroups(ControlGroupId)
    )
    go
    
    -- and selects
    SELECT * 
    FROM view_ABC 
    WHERE 
    	CustSSN = @custssn and
    	Cust.PurchType in 
    	(
    		select Code 
    		from dbo.GroupCodes
    		where ControlGroupId = @ControlGroupId
    	)
    
    
    If the Codes have more attributes, you probably want to maintain another table for the Codes and use GroupCodes table as the references table for Many-to-Many relations.


    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, November 2, 2010 3:08 PM
  • Dmitri,

    Yes you explained my second approach far more lucid and succint than I did. What I was thinking is that the Control Tables would be updated manually by the business analyst. It seems as though my Products example is not a great one. Mostly these hard codes are for business rules so it was my thought to make them into ControlTables then throw a Web GUI over the tables allowing the business analyst to change when desired. It happens kinda regularly as they add / remove / change according to the market and we not having to do pushes constantly.

    Tuesday, November 2, 2010 3:34 PM
  • Hi zzpluralza,

    I understand your point about discontinued etc. being an attribute of the products entity and so my simple example was thus defeated. Let me try it this way. We have a query that looks for 5 specific products out of say 5,000 products. So should I have a control table named says Core5ProdsControl and it would have 5 rows. 1 for each product and then the above mentionedAND Cust.PurchType in ('TSFA','NTSA', 'AUTS', .....) can be replaced with a join. By this logic I would then also have a table for Least5ProdsControl, and BestSell5ProdsControl, and MostReturnedProdsControl etc.

    These extra examples are also attributes of the Productss table. What
    products are core products is a management decision; this attribute
    should be modeled as a column in the product table. The top 5 best
    selling products is a very dynamic attribute, that usually will not be
    stored, but computed when needed. (Unless the computation is very
    expensive, the attribute is queried often, and the business can accept
    this attribute being out of date a bit at ties - in that case, you
    could consider computing the top 5 best sellers at given intervals and
    storing the result -again, in a column in the Products table.

    So as I understand it what you are suggesting is actually a third option....No control tables or unlikely as these examples could all be attributes of the products table. That could get interestring as we have to play ball with a dinosaur known as the AS400 and getting them to change is like telling them there's a meteor hurtling towards them. In other words anything we suggest they see as their possible extinction....which is another thread altogether.

    Yes, that is the idea. Though the AS400 and the associated red tape
    for getting changes through is new information. But not information
    that suddenly makes me change my mind.
    You obviously ARE able to create tables on SQL Server, without going
    through all the hoops associated with changing "the dinosaur". So
    instead of making one or more control tables, you can make a table
    "ProductsExtraAttributes" (or maybe even use a name that actaully IS
    appealing). The rows in this table are associated through a 1:1
    relationship (not enforced) to the Products table - preferably by
    using the same primary key that is also the key in the AS400 table.

    It should not be any extra work for the people who have to maintain
    these attributes - whether they add a product and remove another one
    to a list, or add a value in a row and remove it in another row, the
    amount of work remains the same.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, November 2, 2010 8:01 PM
  • Yes the data is replicated to SQL as an ODS and thus I could as you say create an attributes table. This is appealing and a scenario I had not considered. I'm going to create a few test table and play with it.

     

    Thank You very much for the educational perspective.

    JB

    Tuesday, November 2, 2010 8:28 PM