Junction table design options

[mksql]

As an example, I am building an authentication mechanisim that will use
data in the 3 left tables to determine rights to objects in a
destination table, diagrammed below. In this structure, multiple
records in the left tables will point to multiple records in the right
table. Normally, I would approach this problem using junction tables
(LeftID, RightID) to create many-to-many joins.

However, given the structure of each table is nearly identical (as far
as the linking IDs are concerned), I could also use a single junction
table with columns for each available table ID (LeftID1, LeftID2,
LeftID3, RightID). In this table, only two IDs would be utilized per
row (LeftIDx -> RightID).

In both designs, the needed rights information is returned from fairly
simple views, thus the end result is equivalent. The advantage to the
second, multi-ID junction table design, is a simpler database
structure. However, never using this approach before, I am unsure of
the potential future performance impacts.

Any significant downsides to this second design? Examples of an
abbreviated structure follow:

Data Tables
-----------
LeftTable1
LeftID1 (int)
Data1

LeftTable2
LeftID2 (int)
Data2

LeftTable3
LeftID3 (int)
Data3

DestinationTable
RightID (int)
Data
Linking tables option 1
-----------------------
JunctionTable1
LeftID1
RightID

JunctionTable2
LeftID3
RightID

JunctionTable3
LeftID3
RightID
Linking table option 2
----------------------
Junction
ID1 (int)
ID2 (int)
ID3 (int)
DestinationID (int)

[Erland Sommarskog]

(mk***@yahoo.com) writes:

As an example, I am building an authentication mechanisim that will use
data in the 3 left tables to determine rights to objects in a
destination table, diagrammed below. In this structure, multiple
records in the left tables will point to multiple records in the right
table. Normally, I would approach this problem using junction tables
(LeftID, RightID) to create many-to-many joins.

However, given the structure of each table is nearly identical (as far
as the linking IDs are concerned), I could also use a single junction
table with columns for each available table ID (LeftID1, LeftID2,
LeftID3, RightID). In this table, only two IDs would be utilized per
row (LeftIDx -> RightID).

There is no general answer to this question. You should pick the one that
best describes the business problem. However, that said, it seems a lot
more probable that the three-table design is the right one.

To wit, one single table is right when there is a correllation in the
many-to-many relationship. If the RightID-entity always has a relation
with the three different LeftID-identity, and these there together
compose a set, then that design makes sense. But if the relations are
unrelated to each other, there is no sense to have a common table. And
the latter scenario seems much more likely.

What you have missed to consider is whether the columns are to be
nullable or not. In a junction table, the primary key is typically
the two keys from the joined tables. In a table that joins four different
tables, all four keys would compose the key of the four-join table.
And remember that primary keys can not have NULL values.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp