Help me define the tables in this scenario

database-designoracle

We have 2 tables setup like this:

WEB_GK_STATE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

WEB GK
    PRG_CODE    Varchar2(5)
    TEXT        Varchar2(4000)

Here is some sample data:

INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'OL');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('RC', 'TX');
INSERT INTO WEB_GK_STATE (`PRG_CODE`, `STATE`) VALUES ('PA', 'TX');

INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('PA', 'THIS IS THE GK FOR PA');
INSERT INTO WEB_GK (`PRG_CODE`, `TEXT`) VALUES ('RC', 'THIS IS THE GK FOR RC');

We use this Oracle function to retrieve the values:

CREATE OR REPLACE FUNCTION MAIN.F_GET_GK_TEXT (P_PRG_CODE IN MAIN.WB_GK_STATE.PRG_CODE%TYPE
                                             , P_STATE IN MAIN.WB_GK_STATE.STATE%TYPE)
    RETURN varchar2 IS V_STR varchar2(2000);
BEGIN
    for recs in (select TEXT from wb_gk_state a, WB_GK b
                 where state=p_state and a.prg_code=p_prg_code 
                       and a.prg_code=b.prg_code) loop
           V_STR:= recs.text;
    end loop;
    return V_STR;
END;
/

Now, our dilemma is that we want to be able to provide a different WEB_GK TEXT for the PRG_CODE 'PA' WHERE STATE = 'TX'

If we do it the way we have now, the WEB_GK TEXT will show the same for both OL and TX because we key on PRG_CODE.

My first thought would be to have a STATE column in WEB_GK and get rid of the WEB_GK_STATE table altogether,
but that means we would have to write 50 records to support the same WEB_GK for 50 states.

How could I do this with minimal disruption of our code and modification of our tables?

Best Answer

My answer assumes that PRG_CODE is the primary key of both tables.. (or PRG_CODE + STATE is pkey of WEB_GK_STATE .. but PRG_CODE for WEB_GK)

My suggestion would be to redefine the PK of your WB_GK_STATE table..

WEB_GK_STATE
    WEB_GK_STATE_ID INTEGER PRIMARY KEY
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

Then alter your other table's PK as well.

WEB_GK
    WEB_GK_STATE_ID    INTEGER REFERENCES WEB_GK_STATE (WEB_GK_STATE_ID)
    TEXT        Varchar2(4000)

But that's only one possible solution.

Naturally, there would be several steps required to achieve this - this is simply what the end version of the tables would look like..

The advantage is that the insert/select from WEB_GK_STATE would be pretty much unaltered (assuming you have a trigger to give the WEB_GK_STATE_ID a valid value). Your stored procedure would have to be altered (minimally), and any SQL in the code that looked at the two tables would need to be reviewed and have it's JOIN condition (if there is one) altered.. I don't think that would be much work, though.

EDIT

Given further information, instead of changing your existing two tables, I would add a third..

WEB_GK_STATE_OVERRIDE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)
    TEXT        VARCHAR2(4000)

You would then need to edit your MAIN.F_GET_GK_TEXT function to first look at the WEB_GK_STATE_OVERRIDE function and see if an entry exists for PRG_CODE/STATE .. if so, return the TEXT value .. if not, return the TEXT value recorded in the WEB_GK table.

But your existing design and this one will be fragile.. They will work, but it's very much a bandaid instead of an actual good design that will be flexible in the long run.

Related Question