Role/Person Table?

From: RLC <rchrismon_at_fragomen.com>
Date: 5 Dec 2005 09:03:58 -0800
Message-ID: <1133802238.082200.263230_at_g44g2000cwa.googlegroups.com>

We're developing an application request/packaging/rollout worflow application for our 50 site, 40,000 user company. There is a request table, an engineering table, a distribution table, etc. etc. But, the company has a designated "Application Owner" at each site, and each person who will use the application must also be listed in the workflow application. So, we need a lookup table for the owners and users:

CREATE TABLE REQUEST (

   RQ_ID INTEGER NOT NULL,
   RQ_BY_ID INTEGER NOT NULL,
   RQ_FOR_ID INTEGER NOT NULL,

   ASSIGNED_ENGINEER_ID INTEGER NOT NULL,    OTHER INFO...
);

CREATE TABLE APP_OWNERS (
    RQ_ID INTEGER NOT NULL,
    OWNER_ID INTEGER NOT NULL
);

CREATE TABLE APP_USERS (
   RQ_ID INTEGER NOT NULL,
   USER_ID INTEGER NOT NULL
);

There are many other tables, of course, some with single person ID fields and addititional lookup tables where there are multiple people involved like testers, package distributors, etc. I began to wonder, why not just a single table to cover ALL the people involved:

CREATE TABLE RQ_WORKFLOW_PEOPLE (
  RQ_ID INTEGER NOT NULL,
  PERSON_ROLE VARCHAR(20) NOT NULL,
  PERSON_ID INTEGER NOT NULL
);

INSERT INTO RQ_WORKFLOW_PEOPLE (rq_id,person_role,person_id) values
(123456,'RQ BY',314159),
(123456,'RQ FOR',951413),
(123456,'APP OWNER',159413),
(123456,'APP OWNER',413159),
(123456,'USER',594131),
(123456,'USER',313459),

....

The real question I have is how does one evaluate options like this? The good news, I think, is that where I simply must have cross reference tables because of multiple values (application owners, users, testers, etc.) I've reduced the number of those tables to one by specifying a single table by role. Is that a good thing or a bad thing? I've also removed similar data from several other tables where only a single column was needed in those tables, i.e. the requested by and requested for fields, the assigned engineer, and several others. There is one and only one of each for each request but the type of data, that is an employee ID is exactly the same, so does it make more "sense" to keep the data with the request table or the engineering table or consolidate all ID data in an ID table?
Any thoughts on this woudl be appreciated. Randy Received on Mon Dec 05 2005 - 18:03:58 CET