Need help designing table of rules to be used for SQL selections

I'm refining a script that I have so that it could be automated & maintained by end users.

Previously, I mentioned a portion of this script in this SO post.

As Randy describes in his answe开发者_高级运维r, the logic of the SQL statement should be replaced with a combination of tables and joins to accomplish the same end result.

---

The basic situation and objective are as follows:

Currently, the script will automatically assign advisors to students based on a complex set of rules involving several variables.

The rules for terminal scenarios will change over time due to changes in staff (advisors hired/fired), majors (programs added/removed), and credit hour constraints (minimum hours needed to determine advisor).

These rules will need to be maintained outside of the script/SQL so that end-users (deans/department heads) can manage the terminal scenarios.

A custom table needs to be created to manage these rules effectively.

---

Here's the SQL that is currently used to enforce these rules:

SELECT DISTINCT     s.id stu_id,
                    stu_id.fullname stu_name,
                    p.major1 major,
                    p.minor1 minor,
                    s.reg_hrs,
                    NVL(st.cum_earn_hrs,0) ttl_hrs,
                    p.adv_id curr_adv_id,
                    adv_id.fullname curr_adv_name,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    (1165)
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    (35808)
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    (9179)
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    (70897)
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    (52125)
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    (24702)
                            WHEN    (p.minor1 = 'NURS')                                     THEN    (51569)
                            WHEN    (p.major1 = 'LEG')                                      THEN    (13324)
                            WHEN    (p.major1 = 'CC')                                       THEN    (73837)
                            WHEN    (p.major1 = 'CCRE')                                     THEN    (1133)
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    (9238)
                            ELSE    (p.adv_id)
                    END     new_adv_id,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('Deborah')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('Karen')
                            WHEN    (p.major1 = 'LEG')                                      THEN    ('Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('Naomi')
                            WHEN    ((p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                                    OR (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                                    OR (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                                    OR ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat <> 'A' OR max_stu <= 0))
                                    OR ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0)))
                            THEN    ('Staff')
                            ELSE    (adv_id.fullname)
                    END     new_adv_name,
                    CASE    WHEN    (p.adv_id <> 35808 AND p.major1 = 'NS')                 THEN    ('NS majors not assigned to Veronica go to Debbie')
                            WHEN    (p.adv_id = 35808 AND p.major1 = 'NS')                  THEN    ('NS majors stay with Veronica')
                            WHEN    (p.adv_id = 9179 AND p.major1 = 'DART')                 THEN    ('DART majors stay with Stella')
                            WHEN    (p.minor1 IN ('RT','RESP') AND st.cum_earn_hrs >= 24)   THEN    ('RT-RESP minors go to Lisa')
                            WHEN    (p.major1 IN ('CDSC','CDSD'))                           THEN    ('CDSC-CDSD majors go to Joanne')
                            WHEN    (p.major1 IN ('CA','CB'))                               THEN    ('CA-CB majors go to Barbara')
                            WHEN    (p.minor1 = 'NURS')                                     THEN    ('NURS minors go to Karen')
                            WHEN    (p.major1 = 'LEG')                                      THEN    ('LEG majors go to Nancy')
                            WHEN    (p.major1 = 'CC')                                       THEN    ('CC majors go to Alberta')
                            WHEN    (p.major1 = 'CCRE')                                     THEN    ('CCRE majors go to Naomi')
                            WHEN    (p.adv_id IN (SELECT DISTINCT id FROM fac_rec WHERE stat = 'I'))
                            THEN    ('Current advisor is inactive')
                            WHEN    (st.cum_earn_hrs < 24 AND (p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0 AND min_hrs >= 24))
                            THEN    ('Total credits for this student did not meet the advisor reqs for this major')
                            WHEN    (s.id NOT IN (SELECT DISTINCT stu.id FROM stu_acad_rec stu, sess_info si WHERE stu.yr = si.prev_yr AND stu.sess = si.prev_sess AND stu.reg_hrs > 0 AND stu.reg_stat IN ('C','R') AND stu.prog = 'UNDG'))
                            THEN    ('This student did not attend '||si.prev_sess||si.prev_yr)
                            WHEN    ((p.adv_id||p.major1) IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE (stat <> 'A' OR max_stu <= 0)))
                            THEN    ('Current advisor is not advising students with this major')
                            WHEN    ((p.adv_id||p.major1) NOT IN (SELECT DISTINCT (id||major) FROM adv_detail WHERE stat = 'A' AND max_stu > 0))
                            THEN    ('Current advisor is not advising students with this major')
                            ELSE    ('Student will stay with current advisor')
                    END     change_comm
FROM                stu_acad_rec s,
                    prog_enr_rec p,
                    OUTER stu_stat_rec st,
                    id_rec stu_id,
                    id_rec adv_id,
                    sess_info si
WHERE               s.id = p.id
                    AND s.id = st.id
                    AND s.id = stu_id.id
                    AND p.adv_id = adv_id.id
                    AND s.yr = si.curr_yr
                    AND s.sess = si.curr_sess
                    AND s.reg_hrs > 0
                    AND s.reg_stat IN ('C','R')
                    AND s.prog = 'UNDG'
                    AND p.prog = 'UNDG'
                    AND st.prog = 'UNDG'
                    AND s.id NOT IN (3,287,9238,59999) {System test use IDs}
INTO TEMP           stu_list
WITH NO LOG;

---

I'm trying to build a table to hold all these rules, but have never created a table with this type of purpose.

My idea so far is a table with this structure:

adv_assign_rules
----------------
rule_no
curr_adv_id
major1
major2
minor1
minor2
cum_earn_hrs
new_adv_id
rule_desc
rule_no_ref
rule_stat
rule_date

An example row of this table that would accommodate the first case in the SQL selection might look like:

rule_no         1
curr_adv_id     !35808
major1          =NS
major2
minor1
minor2
cum_earn_hrs
new_adv_id      1165
rule_desc       NS majors not assigned to 35808 go to Debbie
rule_no_ref
rule_stat       A
rule_date       2011-09-26 15:02:26.000

Are there any flaws to such a table? Can this type of setup accommodate all of these rules? Does anyone know where to find examples of tables used for similar purposes?

I'm looking for suggestions on improvements and or alternate solutions to this problem. The "else" scenarios in these case statements indicate that no change in the student's advisor is required. Also, the case that has the largest amount of logic in it (last case before the "else" case), defaults the new advisor to 9238 - this indicates that other logic already in place will be used to assign a new adivsor. All cases prior are default scenarios that are special cases that do not follow the rules for assigning new advisors - these are the special cases that I'm trying to recreate in table format.

UPDATE: I'm also looking for functionality to reproduce and/or scenarios. I added two fields called: rule_no & rule_no_ref which is a serial number (auto-increment) and a reference to another rule's serial number, respectively.

Thanks in advance to any help that can be provided!

---

I would go around this slightly different:

1) Create a table rule_type

id 
name 

2) Create a table rule_set

id   -- rule 
type -- relation to talbe 1
name 
staff_group -- relation to the appropriate staff group for that rule
student_group  -- relation to the appropriate student group for that rule
val_int --- for the rules that require numeric number
val_chr --- for rules that require integer values
val_date --- for date values rules
date_start --- start date of a rule
date_end  --- end date of a rule

you can also make fields for interval paranters like integer range from X ... Y like: val_int_start val_int_end or for any other data type for that matter

then create a store procedure that will process the rules and evaluate the appropriate data

The good think about this approach is that you will really rarely need to code something new in the long run