Posted by RLC on 12/06/05 22:12

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

[Back to original message]