473,836 Members | 1,598 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 4065
(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
5884
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
1808
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
3645
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
1976
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
2434
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
2245
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
2727
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
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10255
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
9382
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
7793
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
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4459
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
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
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.