table code values

[luciffer]

I'm just looking for some feedback on an oracle DB setup.

The data modellers at my work are using a format that sees all lookup tables get a index (ID as number(6)) and they also use a a code (varchar(2)). The code value is supposed to be used so that if the index ever changes on items in the lookup fields you can change the code back to the original code value and still get the proper results.

The part I don't get is that the relations between the tables are still based on the index (ID) as they should be. So I have no idea why bother using the code column.

Has anyone ever seen this practice before or can further explain it? Is it common Oracle practice?


Thanks for any replies.

[gandolf989]

I can understand using surrogate keys as you mention. However, I don't understand why the data modeler would then use code, or abbreviations, for the value. Once you say you are using a surrogate key then you should use a descriptive term for the code, which will most likely be more than two characters.

An exception to this would be to use DD or SA to mean checking account or savings account in the banking industry. They are well established code that are used accross the industry. But in any event, if it were up to me, I would not want to make people memorize codes. Simple is always best.

[LKBrwn_DBA]

I have seen this design used in multi-language applications vg. (ID+LANG+CODE+DESCRIPTION)
Also SAP uses this type of design extensively.
Pro's: Queries can transform any id into correct language 'code' or 'description'.
For example:

ID  LANG CODE DESCRIPTION
--- ---- ---- -----------
101 ENG  Y    Yes
101 SPA  S    Si
111 ENG  T    True
111 SPA  V    Verdadero
...etc...

[DaPi]

I've been bitten by this when using Country Code as primary key to a Country table - sounds pretty safe? Then Finland changed from SF to FIN. Using the "synthetic" key to link tables but showing the "natural" key to the users (e.g in a drop-down) would have saved me a lot of pain.

[luciffer]

I understand the concept as DaPi mentions (if the synthetic key changes SF to FIN).

But in our DB, it is setup so that the value in the code column is just the value of the ID column except it is a varchar. We do have multiple languages (English and French) but it is setup like this.

TableA

ID CODE NAME_EN NAME_FR
--- ----- -------- --------
1 1 Administrator Administrateur
2 2 Guest Visiteur

ID (number(6))
code (varchar(2))

TableB

ID CODE USERNAME ROLE
-- ---- -------- ----
1 1 joe.schmoe 1

Where TableB.Role is related to TableA.ID

The dba's tell me i'm supposed to pass them the CODE value from TableA when doing an insert to TableB and then they do a lookup on the code value and get the ID and insert the ID into the TableB.

I don't see how this would preserve the existing data in TableB because if an ID changed in TableA, TableB is still pointing to the ID so you are going to get the code and fields associated with that ID. So know you've just wasted a bunch time creating CODE columns on every table, doing a look up on the code value to get the id and don't get any benefits from it.

[luciffer]

answer from the dba was basically, we've always done that so thats the way it is. The code column offers no real value. It can just be ignored.