Posted by Erland Sommarskog on 12/07/05 00:23

RLC (rchrismon@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]