Hack 60. Create a List of Personalized Parameters

Instead of creating a single value per variable for each user, it may be necessary to associate multiple values with each user for a single variable. Suppose each member of a staff is responsible for multiple students. Each staff member can access a personalized view that they can use as a basis for their own queries.

A member of staff (MoS), William, is responsible for students 84001001, 84001002, and 84001003. Another MoS, Priya, is responsible for students 84001004 and 84001005. Each MoS has their own database system username: William and Priya. You can encode each user's students in the rules table, which has an entry for every student, as shown in Table 8-2.

You can create a personalized view of this table by referencing the current user's login name. The myStudents view is personalizedwhen William runs it, it shows just his students; when Priya runs it, she will see just her students:

CREATE VIEW myStudents AS SELECT student FROM rules WHERE mos=CURRENT_USER;

You can set up the rules table so that each user can insert into myStudent without reference to the current username:

CREATE TABLE rules (mos VARCHAR(20) DEFAULT CURRENT_USER ,student CHAR(8) PRIMARY KEY );

Now Priya can issue the INSERT statement:

INSERT INTO myStudents(student) VALUES ('05001006');

Her username will be put into the mos column from the default value.

The beauty of this method is that you can create many complex and useful queries that are based on a view such as myStudent. Anyone can use these queries, without alterationeach user will see results limited to only those students she is interested in.