Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
36 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, ...
user avatar
0 votes
1 answer
20 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 ...
user avatar
1 vote
2 answers
40 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 ...
user avatar
0 votes
1 answer
37 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 ...
user avatar
0 votes
1 answer
78 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 ...
user avatar
0 votes
0 answers
36 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. ...
user avatar
0 votes
0 answers
60 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 | +-...
user avatar
0 votes
0 answers
37 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 ...
user avatar
1 vote
0 answers
132 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'...
user avatar
  • 111
0 votes
0 answers
12 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 ...
user avatar
0 votes
2 answers
943 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() ...
user avatar
  • 103
-1 votes
2 answers
183 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 ...
user avatar
  • 51
0 votes
3 answers
99 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 ...
user avatar
0 votes
1 answer
317 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 ...
user avatar
0 votes
1 answer
24 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: ...
user avatar
  • 11
0 votes
0 answers
194 views

Insert into junction table and one of the many-to-many tables

I have these two tables, exercises and workouts, each just for this example having only id and name and a junction table between them workout_exercises, since an exercise can belong to many exercises ...
user avatar
  • 101
1 vote
1 answer
49 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 ...
user avatar
0 votes
0 answers
27 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 ...
user avatar
0 votes
0 answers
346 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 ...
user avatar
1 vote
2 answers
122 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 ...
user avatar
  • 29
2 votes
0 answers
34 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 ...
user avatar
  • 121
0 votes
0 answers
30 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 | ...
user avatar
1 vote
1 answer
127 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 ...
user avatar
  • 29
0 votes
1 answer
872 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 ...
user avatar
  • 1
0 votes
1 answer
41 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....
user avatar
0 votes
2 answers
879 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 ...
user avatar
  • 123
0 votes
0 answers
32 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, ...
user avatar
  • 21
0 votes
0 answers
48 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-...
user avatar
5 votes
1 answer
398 views

Recursivly get a Tree Via self joined table

Using other questions here & 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 ...
user avatar
1 vote
3 answers
226 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 ...
user avatar
  • 13
0 votes
1 answer
324 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 ...
user avatar
0 votes
1 answer
140 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 ...
user avatar
0 votes
1 answer
24 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 ...
user avatar
0 votes
0 answers
2k 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 ...
user avatar
0 votes
1 answer
362 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 ...
user avatar
0 votes
1 answer
43 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 ...
user avatar
0 votes
2 answers
218 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 ...
user avatar
1 vote
1 answer
500 views

How to select rows which are mapped to a junction table in a many to many relationship?

I have three tables: locations (has zipcode, location name); professionals (has name); location_professional. I want to select only those rows from locations table where professional_id is 4. What's ...
user avatar
  • 25
0 votes
0 answers
99 views

Help structuring a query with multiple many-to-many relationships

Assume that there are 4 tables, with association tables "between" each of them: Machines Products Option Groups Options Machines may serve many products, and a product may "belong to" many machines. ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
2 votes
2 answers
874 views

Does the relationship between teams and matches have a many-to-many cardinality ratio?

Diagram I created the following diagram: Considerations So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home. Let ...
user avatar
7 votes
3 answers
8k 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 ------...
user avatar
  • 297
2 votes
1 answer
70 views

How to model a set of unique objects?

I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets. Current entity-...
user avatar
  • 121
2 votes
1 answer
79 views

Unique sets of IDs from table

I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified): CREATE ...
user avatar
0 votes
1 answer
253 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 ...
user avatar
  • 103
0 votes
0 answers
22 views

More than one path between M-M tables

I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships. I have 3 user-facing tables: Dupe, Cue, and Take. There is a ...
user avatar
-1 votes
1 answer
632 views

How to normalize CSV invoice data?

For flat data which comes in with fields like: name address status class version user mode CSV files are regularly received, with the above as typical columns. Some fields, such as "class"...
user avatar
  • 121
0 votes
0 answers
24 views

Variation on a join table

So a join table for many-to-many is typically(?) a two-column table with foreign keys to two other tables. I have a colleague that insists on putting an FK in two tables that are FKs to a "join" ...
user avatar
0 votes
1 answer
392 views

How to denormalize many-to-many relation without nested select?

I have the following schema: house has many students, student has one house gender has many students, student has one gender game has many students. student has many games For instance, consider ...
user avatar
1 vote
1 answer
24 views

How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

I have the following three tables: create table users(id int primary key, name varchar); create table groups(id int primary key, name varchar); create table user_groups(uid int references users(id),...
user avatar