Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
98
questions
0
votes
1answer
31 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 ...
0
votes
1answer
12 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 ...
0
votes
0answers
44 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 ...
1
vote
1answer
42 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 ...
0
votes
0answers
9 views
Multiple relationship types. Single M2M or and additional M2M
In my current schema I have researchers, studies, and surveys.
researchers create studies.
studies create surveys.
Right now, the survey table has a study FK (one-to-many) and the study table has ...
0
votes
1answer
36 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
2answers
39 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 ...
1
vote
1answer
27 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 ...
0
votes
0answers
25 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. ...
0
votes
1answer
178 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 ...
0
votes
1answer
30 views
Better solution for creating many-to-many table per entity
I have a table for images.
I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images.
I need to assign images to entities. The obvious solution is to ...
2
votes
2answers
124 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.
...
1
vote
3answers
366 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
------...
2
votes
1answer
39 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-...
2
votes
1answer
54 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
1answer
53 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
...
0
votes
0answers
14 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
votes
1answer
307 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. ...
0
votes
0answers
21 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" ...
0
votes
1answer
70 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
...
1
vote
1answer
23 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
1answer
26 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 ...
0
votes
1answer
13 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
--------------- ------------ -----------------
...
2
votes
2answers
664 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 ...
1
vote
1answer
742 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 ...
1
vote
3answers
87 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 ...
1
vote
2answers
87 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 ...
1
vote
0answers
142 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 ...
2
votes
1answer
705 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 ...
0
votes
1answer
39 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 ...
2
votes
1answer
498 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 ...
1
vote
1answer
1k 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
5answers
227 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 ...
3
votes
0answers
36 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 ...
1
vote
0answers
172 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
2answers
65 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 ...
0
votes
1answer
77 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 ...
0
votes
2answers
38 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
1answer
1k 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, ...
0
votes
1answer
64 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
0answers
170 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 ...
1
vote
1answer
722 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 ...
9
votes
3answers
1k 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, ...
2
votes
1answer
650 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 ...
1
vote
1answer
82 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 (...
8
votes
5answers
1k 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, ...
0
votes
1answer
51 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 ...
0
votes
1answer
1k 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 (
...
2
votes
3answers
274 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 ...
2
votes
1answer
65 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 ...
