Tuesday, October 04, 2005

You can never have too many tables

There is no problem that cannot be solved with a loop and some dynamic SQL.

Problem: Your report can return rows for more than one feature code. How on Earth are you going to store the results?

Solution: Loop through the FEATURES table, creating three dynamically named tables for each feature code. Problem solved.

BEGIN
FOR rec IN
    ( SELECT f.short_code ft FROM features f
      WHERE  f.chargeable = 'Y' )
LOOP
    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t8_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t8_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t7_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t7_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t9_' || rec.ft ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF rec.ft <> 'ZZ' THEN
        EXECUTE IMMEDIATE 'create table r_t9_' || rec.ft || '(' ||
        'report_type varchar2(50),' ||
        'report_date date,' ||
        'switch_id number,' ||
        'feat_count number,' ||
        'rental_rate number,' ||
        'discount number,' ||
        'rental_rating number)' ;
    END IF;
END LOOP;
END;
/

Now, how to write those reports so they populate the right tables? I know...

7 comments:

Noons said...

I'm having trouble closing my mouth: the jaw keeps dropping...

Gabriel Paulovic said...

Excellent example of how you can end up having too many tables. You should seriously rethink this. That you can do it does not mean it is a good idea to do it.

Thai Rices said...

I could tell you about a particularly large merchant banking organisation that struck on the same idea.

Rather than including an internal business unit in the key of their tables they created a complete copy of every single table in a certain anonymous enterprise back end system.

BU_01_Trades
BU_02_Trades
BU_0x_Trades

etc. etc. etc.

Clearly there is some designer "out there" with years of experience peddling this idea.

William Robertson said...

> You should seriously rethink this

LOL! You think we make this stuff up?

This is some code I found at my current site while having a clearout. It was already superceded by some marginally less crappy code before I joined, although it was still present in the live install scripts, which is how I came to notice it. It has now been deleted, trust me.

Gabriel Paulovic said...

Sorry, I though you were actually suggesting this.

Oh, well, the worst design decision I ever saw was: let's do primary key in a distributed database as a random number, the chances that the same primary key is generated at two sites are 1 to zillion something ... without me knowing this actually got into production till I was able to stop it. Of course we got "unique constraint violated" in the first few days.

Thai Rices said...

You don't need a distributed database for that to be a bad idea. A random number is not going to work for a key on a single node either.

Gabriel Paulovic said...

Of course it is a bad idea on a single node too but it is particularly nasty in a distributed database. On a single note you get "unique constraint violated" but how do you reconcile the data in the distributed system ...