Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
148 questions
0
votes
1
answer
835
views
Many to Many association table PK as FK in other table
I have the following tables: school, teacher, school_teacher and student.
school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher.
A student ...
- 67
0
votes
1
answer
447
views
How to perform schema migration from array to many-to-many to lookup table?
I currently am storing values in a column of type array like follows (this is just an example):
CREATE TABLE blog_posts(
id INTEGER PRIMARY KEY,
blog_title TEXT,
tags TEXT[]
);
With the ...
- 101
0
votes
1
answer
58
views
Make a list of stations by checking next_station column value
How to list all station between selected two stations based on next_station_id
For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that ...
- 113
0
votes
0
answers
3k
views
Database design for metro train route stations and timing
I am making a city metro train app, and for that, I need some advice from you all experts on how to design a database for the following requirements.
The metro has multiple lines (see table), and ...
- 113
0
votes
1
answer
629
views
Understanding many-to-many ternary relationship sets
I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this:
Does this signify that every relationship instance of Party will ...
- 111
0
votes
1
answer
53
views
What is the proper way to relate many to many?
I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
0
votes
2
answers
305
views
Exclusion With Many to Many Relationships
I have created a select statement that show all the topics that are assigned to a book. Now I am trying to create a Select statement that shows all the topics that are not assigned to the book. Book ...
2
votes
1
answer
2k
views
How to select rows which are mapped to a junction table in a many to many relationship?
I have three tables:
locations (has zipcode, location name);
professionals (has name);
location_professional.
I want to select only those rows from locations table where professional_id is 4. What's ...
- 135
0
votes
0
answers
234
views
Help structuring a query with multiple many-to-many relationships
Assume that there are 4 tables, with association tables "between" each of them:
Machines
Products
Option Groups
Options
Machines may serve many products, and a product may "belong to" many machines. ...
2
votes
1
answer
3k
views
Creating a database for a scenario involving students, teachers and courses
I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup:
Models
Users(id, first ...
- 121
2
votes
2
answers
2k
views
Does the relationship between teams and matches have a many-to-many cardinality ratio?
Diagram
I created the following diagram:
Considerations
So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home.
Let ...
- 23
7
votes
3
answers
15k
views
Query "all of" across many-to-many relation
Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables.
User
----
id
name
Group
-----
id
name
UserGroup
------...
- 307
2
votes
1
answer
147
views
How to model a set of unique objects?
I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets.
Current entity-...
- 121
2
votes
1
answer
113
views
Unique sets of IDs from table
I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified):
CREATE ...
0
votes
1
answer
377
views
Avoiding a many-to-many relationship cycle between 3 tables
I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database.
The problem is the following.
I have 3 entities (tables):
donors: hold donors' information
...
- 103
0
votes
0
answers
29
views
More than one path between M-M tables
I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships.
I have 3 user-facing tables: Dupe, Cue, and Take. There is a ...
- 1
-1
votes
1
answer
808
views
How to normalize CSV invoice data?
For flat data which comes in with fields like:
name
address
status
class
version
user
mode
CSV files are regularly received, with the above as typical columns. Some fields, such as "class"...
- 121
0
votes
0
answers
26
views
Variation on a join table
So a join table for many-to-many is typically(?) a two-column table with foreign keys to two other tables.
I have a colleague that insists on putting an FK in two tables that are FKs to a "join" ...
- 131
0
votes
1
answer
742
views
How to denormalize many-to-many relation without nested select?
I have the following schema:
house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games
For instance, consider
...
- 101
1
vote
1
answer
37
views
How to fetch those records who appears in many records in many to many relationship in PostgreSQL?
I have the following three tables:
create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id),...
1
vote
1
answer
494
views
Better way to select a matching subset of rows?
DB Fiddle link. I have a many to many table like this:
CREATE TABLE house_to_cats (
id SERIAL PRIMARY KEY,
house_id INTEGER,
cat_id INTEGER
);
-- house with cats 1 and 2: too small
INSERT INTO ...
- 699
0
votes
1
answer
41
views
get all posts with their associative tags without duplicates
Two tables. Post and Tag. Many to Many bi-directional relationship.
Post Table Tag Table Post_Tag Pivot Table
--------------- ------------ -----------------
...
- 105
2
votes
2
answers
7k
views
How to prevent deadlocks in many-to-many insert/update trigger function?
I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point.
I have a tweet table that receives thousands of records per second. One of the columns ...
- 401
1
vote
1
answer
11k
views
A question about foreign keys and many to many relationships
My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
- 78
1
vote
3
answers
2k
views
Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist
Probably best to explain this with an example.
I have 3 tables two of which are 'regular' tables and the third which is a many to many relationship table joining the first two.
I have a stored ...
- 23
2
votes
2
answers
139
views
Bit column vs relationships?
Two examples:
Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc).
Table articles, each article may have or not have ...
- 141
1
vote
0
answers
799
views
Efficiently query product variants from inventory database (avoiding EAV)
I'm building an ecommerce website for fun with three main components: frontend in Angular, MySQL database and a PHP backend that will function as a REST API.
The inventory database consists of ...
- 11
2
votes
1
answer
2k
views
Why MySQL Workbench creates more than two foreign keys for many to many relations?
I'm using MySQL Workbench. I know that an additional table is needed to define a many to many relation. But why sometimes workbench creates this table with more than two foreign keys?
Is it added ...
- 191
0
votes
1
answer
58
views
M:M Table Design - Use of PK
I have a many-to-many table that contains id's from 2 related tables.
For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK?
Or ...
- 533
2
votes
1
answer
3k
views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
- 193
3
votes
1
answer
7k
views
Using JSONB Column or Another Table to Save Relationsips
I tried to search here thoroughly but did not find any answers.
I have a PostgreSQL database, which has two main tables:
documents
users
These two tables have different relations. A user can:
...
4
votes
5
answers
723
views
Get members of a given group from N:M relationship
I have this N:M relationship:
CREATE TABLE auth_user (
id integer NOT NULL PRIMARY KEY,
username character varying(150) NOT NULL UNIQUE
);
CREATE TABLE auth_group (
id integer NOT NULL ...
- 1,561
3
votes
0
answers
217
views
How to design a database for my entity-relationship diagram about Projects, Tasks and Developers
I'm new to database and my entity-relationship diagram is as follows:
According to that I designed my SQL-DDL database statements as shown below:
CREATE TABLE Developer (
DeveloperID int NOT ...
- 131
1
vote
0
answers
430
views
3 table many-to-many relationship
I have 3 table
Partcar
BarbellWheels
Assembly
now, im confused how to manage this table to got connection, cause Partcar and Assembly is many-to-many relationship. But, BarbellWheels will merging ...
1
vote
2
answers
728
views
Select Items that has one item but not the other
I have three tables:
Table1:
ID Name
1 Item1
2 Item2
3 Item3
Table2:
ID Name Type
1 Logistics One
2 Human Resource Two
3 Finance ...
- 532
0
votes
1
answer
274
views
Chained two Many-to-Many relations
I have the following case, which I can only describe as a chain of two many-to-many relationships:
There are three business entities (tables) and two junction tables:
Orders
Products
Features
Orders ...
- 101
0
votes
2
answers
66
views
How to link books of series together?
I have to link all the books in series together but I'm not sure.
I have a SERIES_ID under which there is a name for a series, author, number of books and number book in the series.
I'm not sure ...
0
votes
1
answer
3k
views
Building an bridge table from existing one with the use of the SELECT INTO
I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data.
Problem (in my case N is actually 22, ...
- 55
0
votes
1
answer
78
views
Need help designing a query to pull a list of songs based on ranking and the last play date
I have a database of a list of songs used during a performance.
The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best. The table is linked to tblEvents which ...
1
vote
0
answers
433
views
Multiple many-to-many database relations
SO told me to ask here, so this is just copy-pasted from over there
We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio ...
- 13
1
vote
1
answer
1k
views
Student and Course Relationship - Grade and Attendance
I was reading on this DBA page, about Student and Course relationship, and the use of a junction table. Suppose I wanted to do two things:
Include the student grade for each course
Allow the ...
user136605
9
votes
3
answers
2k
views
How to hint many-to-many join in SQL Server?
I have 3 "large" tables that join on a pair of columns (both ints).
Table1 has ~200 million rows
Table2 has ~1.5 million rows
Table3 has ~6 million rows
Each table has a clustered index on Key1, ...
- 760
3
votes
1
answer
2k
views
What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?
In mongoDB Many-to-Many Relationship Data Modeling by Mark Starkman, he tries to see how many-to-many relationship can be implemented in the MongoDB database.
He suggests the following code will ...
- 149
1
vote
1
answer
90
views
Find next entity with ordered junction table
I have guides which are linked to series (many to many) with a junction table like so:
guides
id etc.. (more columns)
--
1
13
21
30
series
id etc.. (more columns)
--
1
3
4
series_guides (...
- 113
9
votes
5
answers
3k
views
Mutually exclusive many-to-many relationships
I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, ...
- 1,250
0
votes
1
answer
237
views
Model data with many to many and overrides
I'm having some trouble modeling the database for this particular scenario in Postgres:
We have a set of toys that contain several pieces. Each piece is associated with one or more materials. All of ...
- 101
0
votes
1
answer
4k
views
Adding data in a bridge table automatically
Basically the concept is that a customer can place orders from multiple addresses and an address can have multiple customers, like different members of the same family.
CREATE TABLE Customer (
...
- 103
2
votes
3
answers
2k
views
Multiple Many-to-Many Relations
I've never come up against a similar situation and so I'm looking to gather some tips on how to accomplish this. Here's the situation:
I'm creating an application to track and manage sheet music and ...
- 123
2
votes
1
answer
208
views
How to constrain many-to-many to mark a single record as a "default"? [duplicate]
Suppose I have the following entities:
customer(id, name)
template(id, name)
With a junction table:
customer_template(id, customerId, templateId, is_default)
A customer may have any number of ...
- 123
0
votes
1
answer
54
views
Selecting union of results of dataset-producing function when applied element-wise to an array
This problem is somewhat an academic curiosity. I'm attempting to implement a SQL (i.e. not plpgSQL) function which takes an array of input data, transforms each entry to a set of zero or more rows ...
- 153