Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
148 questions
2
votes
2
answers
97
views
Many-to-many: select an entity by matching relationship
I have a many to many relationship implemented:
CREATE TABLE public.message (
id BIGSERIAL PRIMARY KEY,
name varchar(40) UNIQUE NOT NULL
);
CREATE TABLE public.package(
id BIGSERIAL ...
- 23
0
votes
5
answers
167
views
MySQL solution for many to many associative table that scale over billion entries
Scenario
Imagine we have a table user and an item user.
These 2 tables have an associative table called user_item to define a many to manyrelationship.
We start 100 item records
We have 500 Millions ...
- 151
0
votes
1
answer
41
views
Postgres trigger to update records of several parent tables based on values of a many-to-many join table referencing those parent table records
I have a PostgreSQL 15 join table schema.m2mjointable to link records of several parent tables (schema.table_a, schema.table_b, ...) with other records of those same parent tables:
id
ref_table
...
- 384
0
votes
1
answer
34
views
How do I write sensible FOREIGN KEY CONSTRAINTS if the PRIMARY KEYS from two tables can reference multiple of the other table's PK
I am setting up a database for a university and I'm struggling to setup sensible FOREIGN KEY CONSTRAINTS (and even tables) because I have two tables in which the PRIMARY KEYS of each can relate to ...
-2
votes
1
answer
41
views
What is the correct sentence to express the relationship between the user and his direct manager:
What is the correct sentence to express the relationship between the user and his direct manager:
A many-to-many table must be maintained because managers have several employees under them.
A field ...
- 99
0
votes
0
answers
36
views
How to do many to many query that returns dynamic number of columns?
I have 2 tables that have a many to many relationship. An items table and a properties table. They are connected by a junction table. Basic versions of the tables below.
item_name
sword
helmet
axe
...
- 17
0
votes
1
answer
23
views
Alerting system relationships and STI in PostgreSQL
I'm no expert in DBMS and trying to build up an alerting system that can attach itself to many tables, so I figured asking the question would help my architecture.
The thing that may confuse me a ...
- 101
0
votes
1
answer
43
views
Possible options for SQL and NoSQL combination for social media data
I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we ...
- 101
3
votes
1
answer
416
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
(
[...
- 559
1
vote
0
answers
37
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
2
votes
2
answers
604
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 ...
- 21
1
vote
0
answers
54
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
88
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
142
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
119
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
63
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
880
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
353
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
1k
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
377
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
2
votes
1
answer
2k
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 ...
- 123
1
vote
1
answer
310
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
42
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
230
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
814
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
1k
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
436
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
215
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
48
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
30
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
5
votes
3
answers
15k
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() ...
- 153
-1
votes
2
answers
188
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
195
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
1k
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 ...
- 41
0
votes
1
answer
80
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
257
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
34
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
647
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
452
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
131
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
34
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
328
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
97
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
3k
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
89
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
212
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
634
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