Unique Identity Values Across Multiple Tables
In a previous tip, I made an off-handed comment about queries of the following form:

SELECT x,y,z FROM t1 WHERE Condition1
UNION ALL
SELECT x,y,z FROM t2 WHERE Condition2

to the effect that I couldn't think of many situations where that type of query would be useful in a normalized database.

Well I was wrong about that.

Suppose that t1 and t2 are tables that have exactly the same structure, but which are owned by two separate users.

I'm talking about a situation where each user has their own set of normalized data, even if technically, the data when taken as a whole is not normalized. 

It is perfectly reasonable that one might want to create a view which includes both t1 and t2, to be made available to senior management within the adminisphere.

Such a view might look a lot like the query above, only Condition1 and Condition2 would be identical, or completely absent.

SELECT x,y,z FROM t1
UNION ALL
SELECT x,y,z FROM t2

What if x is an identity column, and the primary key for both t1 and t2?

If the value of x is taken from multiple tables then the UNION query above, may return duplicate values of x.

You might ask "So what?". Well what if the administrator has a table of their own, which is linked to this view?

To make this example more concrete, suppose t1 and t2 are lists of projects. Each project manager has their own project table.

In T-SQL, these tables would be referred to as user1.Project and user2.Project respectively.

Each user would then have their own tables representing sub-tasks or time-sheets for each project.

Now suppose our hypothetical administrator has a table containing monthly performance evaluations for each project, and that
table is called adminisphere.Evaluation
Table:  Project
(same structure for user1 and user2)
Field # Name Type
Field1: ProjectID int identity, primary key
Field2: Title varchar(50)

Table:  Evaluation
Field # Name Type
Field1: EvaluationID int identity, primary key
Field2: ProjectID int
Field3: EvaluationDate datetime
Field4: Opinion varchar(50)

I hope you can see the problem here. To use the ProjectID as the foreign key into the project table, our administrator would need to have a separate evaluation table for each project.

That is, unless a view is created which returns a number which is unique across all Project tables.

CREATE VIEW AllProjects AS
SELECT ProjectID*1000+1 AS UniqueProjectID,
Title FROM user1.Project
UNION ALL
SELECT ProjectID*1000+2 AS UniqueProjectID,
Title FROM user2.Project

(I've made an assumption here, that I will never have more than 1000 users).

OK, I could avoid this whole situation by storing both users' data within a single table and then adding a "UserID" column, and then creating a view for each user which selects only their data. The view made available to the adminisphere would then be the whole table.

So which approach is better?

The situation that prompted me to write this article, was one where I didn't have a choice, because the database structure was pre-determined.

In real life, one is frequently stuck with whatever the last DBA left behind, so the decision to go with a single table or a table per user may not be yours, but here's one good reason to choose the "table per user"
model:

All of the access permissions can be handled by the RDBMS. User3 needs to be able to view the data owned by User2?  No problem, just grant them the appropriate permissions.

More importantly, letting the RDBMS manage user access means that it is easier to find out who has permission to access what.

Home About the Author Copyright © 2001 Adelle Hartley