Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
141
questions
3
votes
1
answer
393
views
Are individual indexes necessary on columns that are included in a compound primary key?
I have this M2M join table:
CREATE TABLE [dbo].[RecipientsDonors]
(
[RecipientId] [int] NOT NULL,
[DonorId] [int] NOT NULL,
CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED
(
[...
- 549
1
vote
0
answers
20
views
Search for a record which has an exact set of many-to-many related records
I have a straightforward many-many arrangement:
scans
id
1
2
products
id
1
2
3
product_scan:
scan_id
product_id
1
1
1
2
2
1
2
2
2
3
When I'm about to create a new scan entry with a ...
- 261
1
vote
2
answers
193
views
Design many-to-many join table with additional column
I have product and documentation tables and want to hava a many-to-many relationship between them with an additional condition.
The tables look like this:
create table product (
id bigserial primary ...
- 11
1
vote
0
answers
29
views
How to represent arbitrary logical expressions in a many to many relationship?
I have a Course entity in my database and I want to represent its prerequisites.
A Course can have many prerequisites.
A Course can be a prerequisite for many other Courses
Clearly, this would be ...
- 11
0
votes
1
answer
46
views
Process for loading data into a logically-arranged Access database with many-to-many relationships
I'm struggling with the logic and feasibility of a certain workflow. I have a list in Excel containing DatabaseName, TableName, FieldName, SubtypeName, and DomainName. I'd like to select all of the ...
- 1
1
vote
1
answer
104
views
Get table data not in pivot table
The problem I wish to solve
I have three tables: notification, user, user_viewed_notification. notification and user have a many to many relationship. How do I get the fresh notifications for a ...
- 79
0
votes
1
answer
75
views
Products multiple variants
My query looks like:
select p.id, p.name, vo.value, v.name
from products p
join products_variant_options pvo on pvo.product_id = p.id
join variant_options vo on vo.id = pvo.variant_option_id
join ...
- 123
2
votes
1
answer
43
views
Foreign key references a many-to-may table (Reinforces the access right)
I have this scenario in an e-commerce project:
Category table, Store table, and Bid table.
So, it should work like this:
categories are at the top.
stores can have many categories (let's name it ...
- 23
3
votes
2
answers
411
views
Ensuring that a combination of records in a many to many relationship is unique
We want to enforce that a combination of an entity is unique in a join table.
Suppose we have the following many to many relationship between widgets and combinations
widgets
-------
widget_id
w1 ...
- 131
0
votes
0
answers
128
views
Multiple many-to-many relations to same table
I have a db schema like below.
A user can be part of groups and have personal challenges. Groups can also have challenges related to them, which are then also related to a user (all users in that ...
0
votes
0
answers
184
views
RBAC Data Model Design
I'm trying to implement roles and permissions for an application I'm working on, from my understanding its an RBAC system.
There are Users, Sites, and for each site a user can have different ...
1
vote
1
answer
699
views
2 foreign keys in a many-to-many relationship
Hello database experts!
I'm designing a small database and ran into a situation where two foreign keys are in the same table referring to different rows in the parent table according to different ...
1
vote
0
answers
259
views
Many to Many triangle between three tables
I am having three entities:
Student, Group, Class. All of them have many-to-many relationship:
One student can attend many classes, and many classes may be assigned to one user.
One student can be a ...
- 11
1
vote
1
answer
811
views
Multiple tables for many-to-many mappings or one table with multiple columns?
I'm using Postgres 14 and I have a tags table with a many-to-many relationship with other entities such as posts, comments, pages, etc. I've currently been representing these with tables like ...
- 111
1
vote
1
answer
185
views
Filter table lookup by array value, but returning groupby unfiltered
I have multiple lookup tables and each need to be filtered by an array of values:
select
array_agg(joined_properties.name) as properties,
items.name
from items
left join (
select properties.name, ...
0
votes
1
answer
37
views
Define max number of entities in many-to-many
I want to design a database with a many-to-many relationship with entities students and teams. Each team can have many students and each student can participate in many teams. However each team can ...
- 11
1
vote
2
answers
174
views
many-to-many relationship with limitations
I am working on a database model with a many-to-many relationship. I want to create a database which will have books, users and reviews. Each book can have many reviews and each user can write many ...
- 11
1
vote
1
answer
641
views
Triggers: counting rows in a bridge table after INSERT
I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using ...
0
votes
1
answer
604
views
How to implement insert, update, delete with a many-to-many relationship for tags?
I am creating an application to manage some ebooks and I would like to have tagging functionality.
The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
0
votes
0
answers
242
views
Many-to-many relationship vs one-to-many relationships
Recently I read an article about building a tag table for better seraching post by tags. The author suggested using one-to-many relationship with post table -> tag table with tag as text[] type. ...
- 101
0
votes
0
answers
165
views
Constraints on many-to-many relationship
I have a multi-user task tracker. Users are in table user:
+---------------+---------+
|user_id(int,PK)|name(str)|
+---------------+---------+
|1 |Alice |
|2 |Bob |
+-...
- 101
0
votes
0
answers
41
views
Fetching result from many to many relation tables based on condition
Working on creating a conditional filter query for the following tables. Created a fiddle with data here.
Desired output I want to write a single query which first fetches list of all products from ...
- 101
1
vote
0
answers
1k
views
Many to many relationship, select rows based on values in list
I want to select group_runs based on a list of group_runners.player and I'm having issue getting rid of results that don't have all the values on that list. In this case group_runs with id = 2 shouldn'...
- 111
0
votes
0
answers
24
views
Understanding Laravel relationships better
I want to make a simple project to understand better the Laravel database relationships. So I decided to make a movie storage project which is holding some movie names and some informations about that ...
- 101
2
votes
3
answers
7k
views
Single foreign key for referencing one of multiple tables
Currently I have a schema that looks like this:
create table person(
id uuid primary key default gen_random_uuid() not null,
);
create table car(
id uuid primary key default gen_random_uuid() ...
- 123
-1
votes
2
answers
186
views
Many to Many - Concept
I know there are a lot questions about MANY TO MANY relationship, but none seem to work for me, because it is more of a question about the concept rather than one of code. So I need some clarification ...
- 51
0
votes
3
answers
169
views
Need help with difficult table modeling situation [closed]
Database newbie here with no formal training, trying to train myself. I was looking for a more difficult case to model and came up with following.
Hypothetical ice cream shop where there are ...
- 19
1
vote
2
answers
856
views
Would it be correct to add extra columns to the junction table according to normalization rules?
Hello I have many to many relationship. I learning normalization rules.
I add quantity because quantity column is used only for products associated with the invoice.
My question is: Can I add extra ...
- 31
0
votes
1
answer
75
views
newbie question - how to list people who has not email address related to him/her? [closed]
Sorry for my newbie question: I would like to list all people even those who has no email address in case of using many-to-many relation with superclass-sublclass inheritance.
Please see SQL Fiddle: ...
- 11
1
vote
1
answer
189
views
What is the proper way to validate that a many-to-many relationship will not surpass a certain limit?
In a Postgres DB, I have a relationship as follows:
A Group table has a column userEntranceLimits.
A UserEntrance table has a column userId and a column groupId.
The count of a UserEntrance's ...
- 11
0
votes
0
answers
31
views
Strategies to get more performance in a specific scenario
Introduction
Hi Guys, my name's Joao, i'm currently working as backend and that's my first post.
I'll appreciate any help!
My team are working on a new feature. During the process of development we ...
- 11
0
votes
0
answers
539
views
MySQL Many to Many with additional data in intermediary table
I need to create a Many to Many relationship and understand the need for an intermediate table to act as the link between the two. The understanding I have is the Group of ID's in the intermediate ...
1
vote
2
answers
289
views
Bridging Table Properties
I have a question on bridging tables: if I have a bridging table linking two tables (standard) with the only values in the bridging table being the Primary Keys for both tables it is linking, how ...
- 29
2
votes
0
answers
113
views
Resolving a double many-to-many relationship
I have the following database design between entities.
A Student can belong to many Departments and a Department has many Students. A specific Department has many Courses and one Course can be at ...
- 121
0
votes
0
answers
32
views
Many-To-Many Relation by storing comma-separated ids in a column? [duplicate]
Is it OK to sometimes instead of making a join table for a many-to-many relation, store the ids of entity A in a column of entity B? It would look something like this:
// A
id | name
---------
10 | ...
- 101
1
vote
1
answer
232
views
Understanding linking tables in a many-to-many relationship
I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may ...
- 29
0
votes
1
answer
2k
views
How to Store a Many-to-Many Relationship Between Fact Tables in a Data Warehouse
What are some structures and models to store many-to-many relational data between two fact tables in a data warehouse? Currently, I am using a mapping table which includes the primary keys from both ...
- 1
0
votes
1
answer
67
views
Why do these queries deadlock and how to work this around in MariaDB?
The following 2 queries enounter DB deadlock in my program:
DELETE FROM proxies
WHERE NOT EXISTS (
SELECT 1 FROM proxy_xref_proxy_list AS pxpl
WHERE pxpl.ProxyID=proxies.ID
AND (pxpl....
- 115
0
votes
2
answers
2k
views
Mysql optimize slow performing query with many to many table joins
I have the following query with joins to a number of many-to-many junction tables:
profile_language
profile_industry
profile_contract_type
profile_contract_hour
profile_qualification
It takes around ...
- 155
0
votes
0
answers
84
views
Filling a database (from a temporary database) with a Many to Many relation
I have a temporary table that I fill in using a CSV file.
CREATE TEMP TABLE temp_products(
product_marketplace_name varchar,
product_link varchar,
product_name varchar,
...
- 21
0
votes
0
answers
166
views
How to handle table that has an id which references multiple dimension tables?
I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-...
7
votes
1
answer
1k
views
Recursivly get a Tree Via self joined table
Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.
However adding a WHERE clause is giving me trouble.
Problem:
A Category can have many ...
- 171
1
vote
3
answers
528
views
Query to seed many to many table
How can I write a query to seed a many to many table. Let's say I have an Employee table, a Branch table, and I am introducing an EmployeeBranch table for the many to many relationship.
I need to ...
- 13
0
votes
1
answer
667
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
347
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
47
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
571
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
46
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
298
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 ...
