Stack Exchange
Help me define the tables in this scenario
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?

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.

It doesn't look to me like I could have different WB_GK_ TEXT for the same PRG_CODE but different STATE. There are no ids or index on either table.

PRG_CODE = 'PA', STATE = 'TX' and PRG_CODE = 'PA', STATE = 'CA' would have different WEB_GK_STATE_ID keys ... which would be linked to different TEXT in WEB_GK via their different WEB_GK_STATE_ID values..

But PRG_CODE='PA', STATE='TX' and PRG_CODE='PA', STATE='CA' should have same TEXT. That is what I'm getting at. The ones that have STATE='OL' or STATE="WB' should have different than with STATE='ANYTHING ELSE'. Is that clearer?

Let's say that we have 3 different GK TYPES and the different types have different TEXT. But one type can be many STATE values while the other 2 types can be only 'OL' and 'WB' STATE values.

That makes sense, and I can only say that your current design is horribly inadequate to support that. The best option I can suggest off the top of my head, then, is to add a table for "override values"... I will amend my answer to include that.

Just to be clear, btw - the design I have in the first half of my answer will still do what you want ... you will just need to have every possible pair of PRG_CODE/STATE defined, many of which would end up having the exact same "TEXT" value in WEB_GK.. There would be lots of data duplication.

I agree and that is what we wanted to avoid.

Actually, I was thinking more long the likes of having a third "cross-reference" table with the TYPE and STATE and PRG_CODE. Leaving the other 2 tables alone.

It's sounding like I can't really comment/advise any further without understanding the business requirements of the said changes. shrug Regardless, I do think that anything beyond what you said near the bottom of your answer or what I have already said will require much more substantial changes.
