473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Role/Person Table?

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
2 1747
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ralf | last post by:
Hi ! When I insert a new record to a table "table_A" I want to Grant a Role "Role_X" to the User "User_Y". So I made a Trigger who should do this work, but it doesn't: When I write: "GRANT...
2
by: Ted | last post by:
How do I grant all privileges for a schema that has a large number of existing tables, procedures, functions, etc to a newly created role, without having to issue a grant statement for each object...
1
by: Tom Dauria | last post by:
I have a SQL database with an Access front end. In the database I have a read only and a read write role. When a read only user opens the database I want all the fields on the form to be locked...
2
by: gudia | last post by:
How would I, using a sql script, copy permissions assigned to a user or a role in one or more databases to another user or a role in their respective databases? Help appreciated
9
by: Guadala Harry | last post by:
This inquiry has to do with the client capabilities and Web client experience: I've been developing rich client apps (Windows desktop client exes in n-tier architecture) for over 10 years and am...
0
by: tmueller | last post by:
Hello, I have a requirement where a person can have multipe roles. Each role has different web navigation and rights to pages. Is there a way to have say a drop down when a person is logged...
1
by: JohnC | last post by:
I'm in the concept stage of putting together a web application. Certain featues will be available only if the person has been given access. This is based on their particular subscription. I can...
3
by: Jo | last post by:
Hi, I know how to create membership user and to define role via "Administer website" in design mode of e.g. an ASP.NET login control. My questions are: 1) how to create a membership user...
4
by: alexandis | last post by:
We have tables of logins (users), that differs much from standard microsoft structure - we don't use control question/answer, date fields, etc. But instead we have several additional fields. I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.