SQL & PL/SQL

Hi all,

As always when i come to this point in PL/SQL and Database development i ask my self what to do. So i am asking you guys and girls what do you do when you come to the same point as i did.

This is just example

Table COM_TO_DO_STATUS with values

1    OPEN

2    TAKEN

3    CLOSE

1. Question. Here i have two options i can create table like this and make FK in my master table or i can make in master table columns STATUS varchar2(5) with check constraint on this values. What do you do?!

With this i know something if i need to add new status in one case i need to insert data in another i need to alter check constraint but have in mind this data or value will have impact on my business logic (sometimes i need to hard code them in my store procedures).

Lets say now i have PKG name is COM_TO_DO_PKG and it has procedure

for first case from top signature is p_send_to_status(p_to_do_id number, p_status_id in number)

for second case from top signature is p_send_to_status(p_to_do_id number, p_status_id in varcahr2)

2. Question. Now i have also two options here, i can create 3 constants in this pkg

c_status_open_taks constant number(1) default 1

c_status_taken_taks constant number(1) default 2

c_status_close_taks constant number(1) default 3

or for second case

c_status_open_taks constant varchar2(5) default 'OPEN'

c_status_taken_taks constant varchar2(5) default 'TAKEN'

c_status_close_taks constant varchar2(5) default 'CLOSE'

with this i am avoiding on some level of hard coding in store procedure, for example

calling this procedure will be like COM_TO_DO_PKG.P_SEND_TO_STATUS(some id,COM_TO_DO_PKG.C_STATUS_CLOSE_TAKS);

also lets say if i have some IF statement code will be like IF L_STATUS = COM_TO_DO_PKG.C_STATUS_OPEN_TASK THEN.....

BUT if i need to hard code them in VIEW i can't use this constants so is there any point with this approach. what do you do?!

3. Question. In this example i have only 3 value, what if you have more like 15 or 20. What do you do?!

4. Question. Sometimes business logic will change so i need to add extra value. In this case what will be best approach?!

Ok guys thank you for your time and sharing experience.

PS sorry if my eng. is not best one.

Thank you.