By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,314 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Role/Person Table?

P: n/a
RLC
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

Dec 6 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
RLC (rc*******@fragomen.com) writes:
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?


It's difficult to assess this with good knowledge of the business
domain. But it reminds of "generaltypes", a table that once existed in
the database that I work with, many years ago. (And it came into existence
before I joined the company.) The observation was there was many type
tables, that typically only had two columns: a code and a descriptive name.
Thus, the idea came about that rather having many tables, there would
be one single that had three columns: the code, the type code and the
descriptive name.

What's bad with this? Well, if you want a foreign-key cosntraint, you
find that a referencing table needs to have two columns for each code,
and one of these column would be a constant, the type code. That could
in some cases buy you 5-6 extra columns in a table.

Another problem is that if one of these entities grows in complexity
and acquires attributes of its own, it does no longer fit into the
generaltypes table.

Now, in your case, it appears that all these tables do define people,
so it's is a more constrained concept. Particularly, I assume that
ID 12345 will always refer to the same person, no matter the role.
(Whereas in "generaltypes", the code "ABC" could be used for two
different entities without having anything in common.) And furthmore,
I would assume that 12345 is defined in some general pepople table
anyway.

And while you could add constant columns to enforce that only persons
of the right role appears, you could also do this with triggers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 6 '05 #2

P: n/a
i've seen both ways.
In the real world, people can have multiple roles, and they can change
over time.
So, a list of people and a list of roles, and a list of projects.
Then a list of valid CURRENT roles people can have. and a list of of
people assigned to a project, and their role.
You can also have a "list of required roles for any new projects."

A user can be an engineer, can be an tester, can be a requestor, can be
a whatever. when all done, you need a valid list of PEOPLE.
What about those projects with more then one requestor? my solution
covers it.

Dec 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.