Stack Overflow
Stack Overflow

Questions tagged [junction-table]

A junction table is a table that contains common fields from two or more tables and is the canonical way to implement Many-to-Many relations in SQL and Relational-type databases.

In database management systems following the relational model, a junction table is a table that contains common fields from two or more tables. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names such as bridge table and linking table.

221 questions
106
votes
8 answers

How to retrieve the last autoincremented ID from a SQLite table?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text). I have a table Users with columns username (primary key, text) and Hash. A message is sent by one Sender (user) to many recipients (user) and a recipient (user)…
Dabblernl
  • 15,831
  • 18
  • 96
  • 148
39
votes
3 answers

MS SQL creating many-to-many relation with a junction table

I'm using Microsoft SQL Server Management Studio and while creating a junction table should I create an ID column for the junction table, if so should I also make it the primary key and identity column? Or just keep 2 columns for the tables I'm…
ab1428x
  • 794
  • 2
  • 8
  • 20
32
votes
5 answers

In a join table, what's the best workaround for Rails' absence of a composite key?

create_table :categories_posts, :id => false do |t| t.column :category_id, :integer, :null => false t.column :post_id, :integer, :null => false end I have a join table (as above) with columns that refer to a corresponding categories table and a…
pez_dispenser
  • 4,394
  • 7
  • 37
  • 47
27
votes
8 answers

Why is it not good to have a primary key on a join table?

I was watching a screencast where the author said it is not good to have a primary key on a join table but didn't explain why. The join table in the example had two columns defined in a Rails migration and the author added an index to each of the…
pez_dispenser
  • 4,394
  • 7
  • 37
  • 47
25
votes
1 answer

Junction tables vs foreign key arrays?

I'm modeling many-to-many relationship where the relationship is accessed most of the time from one side only. It's more like a hierarchy, that is accessed top-down and not the other way around. Survey has and belongs to many Questions has and…
randomguy
  • 12,042
  • 16
  • 71
  • 101
23
votes
2 answers

UML class model how to model many to many relationship

I have read several tutorials on what a UML model should contain and what it shouldn't. As a developer, I always think in terms of a relational data model where you could never have a many-to-many relationship between tables. Now, with a UML class…
Peter
  • 14,221
  • 15
  • 70
  • 110
17
votes
3 answers

SQL join on junction table with many to many relation

I have three tables, of which 2 are regular data tables and 1 is a many to many junction table. The two data tables: table products product_id | product_name | product_color ----------------------------------------- 1 | Pear |…
B_s
  • 3,026
  • 5
  • 32
  • 51
13
votes
6 answers

Can a junction table (join table) also be used for a one-to-many relationship?

According to the definition, a Junction Table (bridge table/link table) is used for many-to-many relationships, when used like this: CREATE TABLE Users ( UserLogin varchar(50) PRIMARY KEY, UserPassword varchar(50) NOT NULL, UserName varchar(50) NOT…
pez_dispenser
  • 4,394
  • 7
  • 37
  • 47
13
votes
2 answers

How to configure a One-to-Many relationship in EF

I have the following model public class PageConfig : Base { // Properties Etc.. public ICollection ScrollerImages { get; set; } } My approach is to bind using a junction table { PageConfigID, ImageID }. In my model binder i tried…
opdb
  • 221
  • 1
  • 3
  • 12
10
votes
3 answers

What is the difference between an entity relationship model and a relational model?

I was only able to find the following two differences: The relationships in an E-R model are explicitly defined, while they are implicit in a relational model. Relational models require an intermediate table (often called a "junction table") to…
Shailesh
  • 2,116
  • 4
  • 28
  • 48
7
votes
1 answer

Many to many relationship with junction table in Entity Framework?

I'm trying to create a many-to-many relationship in Entity Framework (code first), according to the following post: Database design for limited number of choices in MVC and Entity Framework? However, I can't get it to work properly, and I'm sure I'm…
Anders
  • 12,556
  • 24
  • 104
  • 151
7
votes
4 answers

Primary Key / Clustered key for Junction Tables

Let's say we have a Product table, and Order table and a (junction table) ProductOrder. ProductOrder will have an ProductID and an OrderID. In most of our systems these tables also have an autonumber column called ID. What is the best practice for…
Yvo
  • 18,681
  • 11
  • 71
  • 90
7
votes
2 answers

How do I query a junction table

Using Entity Framework/LINQ, I need help with the following. The database has a table of People with an identity column of PersonId. There is also a Skills table with an identity column of SkillId. These two are connected via a third table…
Steve Wash
  • 986
  • 4
  • 23
  • 50
4
votes
1 answer

Android Room Many to Many Junction table getting more than relation of 2 tables

I have created a relation between User, Property and junction table of these two items UserPropertyJunction which can be visualized as releation in the image below UserEntity, instead of customers, which is @Entity(tableName = "user") data class…
Thracian
  • 43,021
  • 16
  • 133
  • 222
4
votes
3 answers

Should I store additional data in SQL join/junction table?

Are there any drawbacks to storing addition data in my join table / junction table. For example, I am working on a database of trucking companies and I have 3 tables: Table 1 - company, Table 2 - trailer_type, Table 3 - junction_table, Each company…
zcleft
  • 103
  • 2
  • 8
1
2 3
…
14 15