Lookup tables - what to do when editing/deleting values

[WannaFly]

I've only developed relatively small applications, but they are getting larger and larger.
What do enterprise systems do with a lookup table, for instance:

Lookup_Status
Status_Code Status_Value
B BUY
S SELL
W WAIT

and these values bind to a combobox that a user can select BUY WAIT or SELL.


But now, they want ot change it so the values are
BN BUY NOW
BL BUY LATER
SN SELL NOW
SL SELL LATER
WL WAIT LONG
WS WAIT SHORT

So, a user can go in and delete and add al lthe new rows, but that means and rows stored with the old codes, will now show blank because there isnt a value for them. Is the solution to add an ACTIVE column, and dont allow editing of the code?

Sorry if i'm being too vauge...I'll provide more info if needed.

 

[KLin]

Ask the business process owner what new codes should map to the old codes, then run an update query to fix the old codes manually.


EDIT: Or like you said add an active flag to the codes lookup, add new records and set to active, set old codes to inactive, and only allow the combo box to show active codes.

 

[BoberFett]

Just be cautious if you do delete any old values if you've got the foreign keys set up for cascading deletes. We don't want to see you posting here next week about looking for a job.

 

[KLin]

Originally posted by: BoberFett
Just be cautious if you do delete any old values if you've got the foreign keys set up for cascading deletes. We don't want to see you posting here next week about looking for a job.

QUOTED FOR TRUTH :laugh:

 

[Ka0t1x]

Originally posted by: KLin
EDIT: Or like you said add an active flag to the codes lookup, add new records and set to active, set old codes to inactive, and only allow the combo box to show active codes.

^ my 2c.

Here at work is better to store the old 4-5 records and pack on new ones, the codes are completely irrelevant if you have a text description to show what they mean. Drop an Active column in it and mark them inactive.

 

[WannaFly]

BoberFeet/Klin, thanks for the warning, I wouldn't do that. Part of the reason i ask is because I'm trying to make the software as autonomous without any IT/programmer support. I'm trying to avoid having to run queries manually.

Ka0t1x: That's what i'm thinking. I guess my problem is I always use comboxes and only store the code, not the text. That'd be twice as much info to show on my screen. I gotta figure this one out.

 

[KIAman]

What medium is the lookup table? MS Access? Excel? Flat file? RDBMS?

 

[WannaFly]

KIAman: MSSQL 2005

 

[nakedfrog]

Originally posted by: KLin
EDIT: Or like you said add an active flag to the codes lookup, add new records and set to active, set old codes to inactive, and only allow the combo box to show active codes.

That's what I'd recommend.