Help with scalability design

MickeySox · #1 (**permalink**) 05-28-09, 14:54

Guys,

My first post here, hello to all, thanks in advance for any help you can give.

Ok so I'll cut straight to it, here's the problem.

I have a table 2 tables with the following columns

TABLE1:
SUMMARY_DATE
SORT_CODE
ACCOUNT_NO

which in any given month will only have one sc and acc-no

TABLE2
SUMMARY_DATE
SORT_CODE
ACCOUNT_NO
TYPE
VOL

which in any given month could potentially have the same sc acc-no but a different type.

so

DATE1 SC1 ACC1 TYPE1 VOL
DATE1 SC1 ACC1 TYPE2 VOL

Now when I try and join the 2 tables

Code:

SELECT T1.SUMMARY_DATE, 
       T1.ACCOUNT_NO,
       T1.SORT_CODE,
       CASE WHEN TYPE = 'A' THEN
            VOL
       END AS VOL1,
       CASE WHEN TYPE = 'B' THEN
            VOL
       END AS VOL2,
FROM   TABLE1 T1
       LEFT JOIN
       TABLE2 T2
ON     T1.SORT_CODE = T2.SORT_CODE
AND    T1.ACCOUNT_NUMBER = T2.ACCOUNT_NUMBER
AND    T1.SUMMARY_DATE = T2.SUMMARY_DATE

You can see that the volumes will be duplicated.

This can be solved by joining TABLE2 twice

Code:

SELECT T1.SUMMARY_DATE, 
       T1.ACCOUNT_NO,
       T1.SORT_CODE,
       T2,VOL,
       T3.VOL
FROM   TABLE1 T1

       LEFT JOIN

       TABLE2 T2
ON     T1.SORT_CODE = T2.SORT_CODE
AND    T1.ACCOUNT_NUMBER = T2.ACCOUNT_NUMBER
AND    T1.SUMMARY_DATE = T2.SUMMARY_DATE
AND    T2.TYPE = 'A'
        
        LEFT JOIN

       TABLE2 T3
ON     T1.SORT_CODE = T3.SORT_CODE
AND    T1.ACCOUNT_NUMBER = T3.ACCOUNT_NUMBER
AND    T1.SUMMARY_DATE = T3.SUMMARY_DATE
WHERE  T3.TYPE = 'B'

Another solution to this problem would be to change my table from a row to column base.

so i/o

DATE1 SC1 ACC1 TYPE1 VOL
DATE1 SC1 ACC1 TYPE2 VOL

I would have

DATE1 SC1 ACC1 TYPEA_VOL TYPEB_VOL

The problem with both of these approaches is that I have no room for scalability, if I add any products (TYPE) it would mean altering the code

So I'm looking for a slightly better solution or suggestions?

P.S Table1 cannot be modified to bring TYPE in

Stealth_DBA · #2 (**permalink**) 05-28-09, 16:39

MickeySox, This isn't answering you question directly but...

This should get you the data without duplicating Volume or require the third (fourth, fifth, etc.) joins:

Code:

SELECT T1.SUMMARY_DATE, T1.ACCOUNT_NO, T1.SORT_CODE, T2.VOL001, T2.VOL002
FROM table1 AS T1
       LEFT OUTER JOIN
       (SELECT SUMMARY_DATE, SORT_CODE, ACCOUNT_NO
             , MAX( CASE WHEN TYPE = 'A' THEN VOL END ) AS VOL001
             , MAX( CASE WHEN TYPE = 'B' THEN VOL END ) AS VOL002
        FROM table2
        GROUP BY SUMMARY_DATE, SORT_CODE, ACCOUNT_NO
       ) AS T2
         ON     T1.SUMMARY_DATE = T2.SUMMARY_DATE
            AND T1.ACCOUNT_NO   = T2.ACCOUNT_NO
            AND T1.SORT_CODE    = T2.SORT_CODE

Basically, it is transposing the rows to columns on TABLE2 before joining to table1.

You would still have to alter the code to add a new VOLxxx to the Select and another MAX(CASE...) to the nested table query. The good news is that it would be a much smaller change. Also, the performance wouldn't decrease very much as more TYPEs are added (as opposed to adding another join for every TYPE).

But altering the code is 'almost' unavoidable. I can think of two potential ways around this (they may not be the best ways or even the only ways).

A) If you know what the new TYPEs will be (C, D, E,..., Z), you could go ahead and code all 26 letters of the alphabet (in this example), and have VOL001 through VOL026 in the Select list and 26 MAX(CASE...) statements.

This way as, the new code is added, no changes are needed (until you try to get past Z).

If you didn't like the 'extra' columns on your output, you could create a View with just the current valid TYPES and not all the 'future expansion' TYPES. However, you would still need to change the View as new Types are added.

B) Dynamically build the SQL. I don't use this and may not explain it well. In essence you would build up a character string that contains the SQL you will be running. First you would have to query table2 to find the MAX number of TYPES. Then you would start building the SQL statement:

SQLSTATEMENT = 'SELECT T1.SUMMARY_DATE, T1.ACCOUNT_NO, T1.SORT_CODE'
Loop from 1 to Max types
SQLSTATEMENT = SQLSTATEMENT + ', VOL' + character(loop value)
end loop
SQLSTATEMENT = SQLSTATEMENT + 'FROM...ACCOUNT_NO'
loop from 1 to Max Types
SQLSTATEMENT = SQLSTATEMENT = ', MAX(CASE...) AS VOL' + charter(loop value)
end loop
SQLSTATEMENT + 'FROM...T2.SORT_CODE'

(Problems: How to put in the comparison types ( = 'A', = 'B', etc.) and how to handle embedded apostrophes).

This is VERY rough pseudo code but hopefully you get the idea.

Then you have to prepare/execute the SQLSTATEMENT.

PS Beyond the general idea, I am not very proficient with generating dynamic SQL. If you have question, I probably can't answer them.

gvee · #3 (**permalink**) 05-29-09, 09:22

You need to change your design - whenever you see semething like some_col_1, some_col_2, ..., some_col_n alarm bells should be going off in your head.

Can you describe in words about your design - it's awefully hard to follow as it stands!

Internet Services

Web Development

Digital Marketing

Consumer Tech