473,883 Members | 1,460 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Junction table design options

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

DestinationTabl e
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)

Jul 20 '05 #1
1 4068
(mk***@yahoo.co m) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
5885
by: Marcy | last post by:
I am trying to build a database to keep track of training topics completed by people in my department. Our department has a set of 37 training topics. There are 7 job classifications in the department. Each job classification has a group of required training topics. Some of the training topics are required by more than one job classification. I have the following tables: TblJob JobID JobDescription TblTopic
1
1810
by: Riley DeWiley | last post by:
I have an application with two tables, A and B. Each has an autonumber unique ID field, plus other data. I have a junction table, AB, containing fields AID, BID, and Count (a number). AB has a combined unique key comprising AID and BID, that is, any combination of the two must be unique, even though any given AID or BID may exist in many records.
1
3646
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. One example is a kind of trivia subject. There are several "categories" such as "geography", "history", etc., like in the Trivial Pursuit game. Each card has a question in each category.
4
1978
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best equipment to use for a customer's needs. It basically consists of a table with the following setup: Equipment: Option1: Option2: Option3: Option 4: ... -------- ------- ------- -------- --------
3
2437
by: inthemix | last post by:
My goal is to have a sub-form located on the main form which containts a listbox. The user will be able to select anany number of offices (by officeName) from this listbox. IMO the design is very sound, in 3NF. I just can't figure out how to get the listbox control to work as desired. Table 1 - Applicant Table 2- Interest (Junction) Table 3 - Office
1
3604
by: que576 | last post by:
I have created a junction table so that I can relate data from 2 other tables. Table 1 - Category Table (has the following fields with data) CatID (primary key) CategoryName CategoryStatus Table 2 - Names Table (has the following fields with data) NameID (primary key) Names
2
2249
by: Henry Stockbridge | last post by:
Hi, I need a recommendation when to add a record to a junction table that complements a many to many relationship. There will be a Contacts form, and an Interests subform with the parent/child field being the ContactID. Table 1 - Contacts (ContactID (PK), First, Last, so on) Table 2 - ContactsInterests (ContactInterestsID (PK), ContactID (FK), InterestsID (FK))
1
2729
by: bg_ie | last post by:
I'm designing a database with 3 tables called Function, Test and Scene. A Function has multiple Tests, but a Test has only one Function. A many to many relationship exists between Test and Scene therefore I need a junction table between these two tables - giving 4 tables in total. The Test table would store a foreign key, the primary key of the Function table. There is a problem with design though and that is that Functions and
3
2578
by: dbertanjoli | last post by:
Hello, I have a questinarrie webform I use INSERT statement(s) to insert a new record in the User and Questions tables and then (HERE IS MY PROBLEM) I need to update my junction table (QuestionScores - has two fields; questionid and scoreID). What should be a statement to update my junction table? This is my code except updates to junction table: <% ' Declaring variables Dim tmpFullName, tmpAge, tmpDateDB, tmpMedicalRecord,...
0
11128
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10408
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9568
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7964
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4607
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4212
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.