How do I (quickly) collate IDs from various tables?

Question

I have three denormalized tables that I have to take at face value (data comes from some external resource). The three tables have different definitions, but they each describe the same object from different perspectives.

   object1  A  B
   object2  A
   object3     B  C
   object4        C

The only commonality between these tables is their primary key. I can corral the IDs together using SELECT UNION SELECT, but the query seems relatively slow, even when each table has its PK field indexed. I could create a view to abstract this query, vw_object_ids, but it performs at the same speed. I thought I could add an index to materialize the view, but in SQL Server 2005, you can't index views with UNIONs.

What I want is to have a master index of IDs be in sync with with the underlying data, which may get updated or deleted whenever. I guess I could accomplish this indefinitely with a crazy set of triggers or just settle for the speed of the unindexed view. But I just wanted to make sure I'm not missing any options or if this scenario has a name or is indicative of a pattern.

Thoughts?

Answer 1

Why not just do an outer join and then coalesce the columns from the component tables?

Answer 2

Create a master table that contains only the ID:

CREATE TABLE master (ID INT NOT NULL PRIMARY KEY)

and make all three tables to refer to that master table with ON DELETE CASCADE.

To populate the table for the first time, issue

INSERT
INTO    master
SELECT  id
FROM    a
UNION
SELECT  id
FROM    b
UNION
SELECT  id
FROM    c

To populate the table on a regular basis, create a trigger on each of three tables.

This trigger should try to insert the new ID to master and silently fail on PRIMARY KEY violation.

To query, use:

SELECT  *
FROM    master m
LEFT OUTER JOIN
        a
ON      a.id = m.id
LEFT OUTER JOIN
        b
ON      b.id = m.id
LEFT OUTER JOIN
        c
ON      c.id = m.id

This will use indexes efficienty.

To delete, use:

DELETE
FROM    master
WHERE   id = @id

This will fire ON DELETE CASCADE and delete records from all three tables if any.