Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ( [...
InteXX's user avatar
  • 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 ...
Synchro's user avatar
  • 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 ...
abobov's user avatar
  • 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 ...
devordem's user avatar
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 ...
jdawgx's user avatar
  • 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 ...
bik555's user avatar
  • 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 ...
kicaj's user avatar
  • 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 ...
bguernouti's user avatar
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 ...
jellycola's user avatar
  • 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 ...
thomasmol's user avatar
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 ...
another_one's user avatar
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 ...
JosueMagnus12's user avatar
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 ...
Yolomiys's user avatar
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 ...
mowwwalker's user avatar
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, ...
LittleFoxyFox's user avatar
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 ...
User123's user avatar
  • 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 ...
User123's user avatar
  • 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 ...
Filippo Scaramuzza's user avatar
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 ...
Attitude12136's user avatar
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. ...
cozycoder's user avatar
  • 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 | +-...
programagor's user avatar
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 ...
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
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 ...
cerealikaeme's user avatar
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() ...
filipot's user avatar
  • 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 ...
Dean7's user avatar
  • 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 ...
William S's user avatar
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 ...
Fırat Kaya's user avatar
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: ...
kszemes's user avatar
  • 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 ...
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
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 ...
Michael B.'s user avatar
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 ...
zv426's user avatar
  • 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 ...
jAdex's user avatar
  • 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 | ...
Mateusz Sowiński's user avatar
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 ...
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
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....
Serge Rogatch's user avatar
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 ...
adam78's user avatar
  • 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, ...
bma's user avatar
  • 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-...
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
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 ...
Imran's user avatar
  • 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 ...
Mr. Mars's user avatar
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 ...
ben_frankly's user avatar
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 ...
pixelngrain's user avatar
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 ...
pixelngrain's user avatar
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 ...
Yash Chowdhary's user avatar
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 ...
Nick Seferos's user avatar
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 ...
JAMES MICHAEL OLSON's user avatar