Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
30 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 ...
s.k's user avatar
  • 372
0 votes
1 answer
26 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 ...
heeeresjohnny's user avatar
-2 votes
1 answer
39 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 ...
Pedro Gómez's user avatar
0 votes
0 answers
26 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 ...
Brian Hung's user avatar
0 votes
1 answer
20 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 ...
Laurent's user avatar
  • 101
0 votes
1 answer
35 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 ...
dksr's user avatar
  • 101
3 votes
1 answer
402 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 ( [...
InteXX's user avatar
  • 559
1 vote
0 answers
26 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 ...
Synchro's user avatar
  • 261
1 vote
2 answers
421 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 ...
abobov's user avatar
  • 11
1 vote
0 answers
37 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 ...
devordem's user avatar
0 votes
1 answer
70 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 ...
jdawgx's user avatar
  • 1
1 vote
1 answer
130 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 ...
bik555's user avatar
  • 79
0 votes
1 answer
96 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 ...
kicaj's user avatar
  • 123
2 votes
1 answer
46 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 ...
bguernouti's user avatar
3 votes
2 answers
693 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 ...
jellycola's user avatar
  • 131
0 votes
0 answers
304 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 ...
another_one's user avatar
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 ...
JosueMagnus12's user avatar
1 vote
0 answers
341 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 ...
Yolomiys's user avatar
1 vote
1 answer
1k 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 ...
mowwwalker's user avatar
1 vote
1 answer
260 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, ...
LittleFoxyFox's user avatar
0 votes
1 answer
41 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 ...
User123's user avatar
  • 11
1 vote
2 answers
215 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 ...
User123's user avatar
  • 11
1 vote
1 answer
772 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 ...
Filippo Scaramuzza's user avatar
0 votes
1 answer
901 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 ...
Attitude12136's user avatar
0 votes
0 answers
382 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. ...
cozycoder's user avatar
  • 101
0 votes
0 answers
199 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 | +-...
programagor's user avatar
0 votes
0 answers
46 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 ...
coderunner's user avatar
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'...
Oen44's user avatar
  • 111
0 votes
0 answers
28 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 ...
cerealikaeme's user avatar
4 votes
3 answers
12k 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() ...
filipot's user avatar
  • 143
-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 ...
Dean7's user avatar
  • 51
0 votes
3 answers
192 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 ...
William S's user avatar
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 ...
Fırat Kaya's user avatar
0 votes
1 answer
78 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: ...
kszemes's user avatar
  • 11
1 vote
1 answer
234 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 ...
Léo Vital's user avatar
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 ...
João Castro's user avatar
0 votes
0 answers
613 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 ...
Michael B.'s user avatar
1 vote
2 answers
406 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 ...
zv426's user avatar
  • 29
2 votes
0 answers
129 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 ...
jAdex's user avatar
  • 121
0 votes
0 answers
33 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 | ...
Mateusz Sowiński's user avatar
1 vote
1 answer
295 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 ...
zv426's user avatar
  • 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 ...
Edward's user avatar
  • 1
0 votes
1 answer
84 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....
Serge Rogatch's user avatar
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 ...
adam78's user avatar
  • 155
0 votes
0 answers
88 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, ...
bma's user avatar
  • 21
0 votes
0 answers
194 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-...
danieltisdale's user avatar
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 ...
dustytrash's user avatar
1 vote
3 answers
607 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 ...
Imran's user avatar
  • 13
0 votes
1 answer
790 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 ...
Mr. Mars's user avatar
0 votes
1 answer
420 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 ...
ben_frankly's user avatar