Skip to main content

Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 vote
2 answers
466 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 ...
1 vote
4 answers
119 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 ...
1 vote
1 answer
782 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 ...
2 votes
1 answer
3k 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
2 answers
302 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 ...
0 votes
1 answer
607 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
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 ...
0 votes
1 answer
28 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
40 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 ...
0 votes
0 answers
27 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 ...
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 ...
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 ...
-1 votes
1 answer
336 views

Choosing Primary & Foreign Keys & normalizing Person, Name, DOB & Wedding tables

I am in the conceptual design of a database, building tables and views, selecting the PKs and FKs (primary and foreign keys) and normalizing. Unfortunately, I am constrained to use Power Pivot on ...
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() ...
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 ...
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 ( [...
2 votes
2 answers
7k 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
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 ...
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 ...
0 votes
1 answer
71 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 vote
1 answer
133 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 ...
0 votes
1 answer
98 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 ...
14 votes
2 answers
9k views

Do I need a separate Id column for this "mapping" table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
2 votes
1 answer
48 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 ...
3 votes
2 answers
713 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 ...
0 votes
1 answer
227 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 ...
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 ...
7 votes
3 answers
14k 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 ------...
0 votes
0 answers
310 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
347 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 ...
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 ...
3 votes
1 answer
6k 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: ...
1 vote
1 answer
267 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
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 ...
1 vote
2 answers
216 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 votes
5 answers
35k views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
0 votes
1 answer
924 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
398 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. ...
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 | +-...
2 votes
1 answer
3k 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 ...
3 votes
1 answer
504 views

Detect circular relations in a distribution table

I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself). create ...
0 votes
0 answers
621 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 ...
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 ...
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'...
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 ...
1 vote
1 answer
89 views

Many-many relation with specific type

Sorry if this question has already been asked, but I didn't find it performing a search. I have 2 tables (table_a and table_b), linked by a many/many relation. The relation can be of two types, ...
-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 ...
0 votes
1 answer
371 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 ...