Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [many-to-many]

The tag has no usage guidance.

1
vote
1answer
16 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),...
1
vote
1answer
22 views

Better way to select a matching subset of rows?

DB Fiddle link. I have a many to many table like this: CREATE TABLE house_to_cats ( id SERIAL PRIMARY KEY, house_id INTEGER, cat_id INTEGER ); -- house with cats 1 and 2: too small INSERT INTO ...
0
votes
1answer
10 views

get all posts with their associative tags without duplicates

Two tables. Post and Tag. Many to Many bi-directional relationship. Post Table Tag Table Post_Tag Pivot Table --------------- ------------ ----------------- ...
2
votes
2answers
85 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
1answer
39 views

A question about foreign keys and many to many relationships

My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
1
vote
3answers
53 views

Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist

Probably best to explain this with an example. I have 3 tables two of which are 'regular' tables and the third which is a many to many relationship table joining the first two. I have a stored ...
1
vote
2answers
83 views

Bit column vs relationships?

Two examples: Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc). Table articles, each article may have or not have ...
1
vote
0answers
61 views

Efficiently query product variants from inventory database (avoiding EAV)

I'm building an ecommerce website for fun with three main components: frontend in Angular, MySQL database and a PHP backend that will function as a REST API. The inventory database consists of ...
1
vote
1answer
242 views

Why MySQL Workbench creates more than two foreign keys for many to many relations?

I'm using MySQL Workbench. I know that an additional table is needed to define a many to many relation. But why sometimes workbench creates this table with more than two foreign keys? Is it added ...
0
votes
0answers
26 views

Same relationship between tables

I have two table that have a binary relationship with each other but also have this same relationship in a unary manner with themselves. Here's a diagram of the realtionships: Based on the rest of ...
0
votes
1answer
36 views

M:M Table Design - Use of PK

I have a many-to-many table that contains id's from 2 related tables. For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK? Or ...
2
votes
1answer
199 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 ...
0
votes
1answer
308 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: ...
4
votes
5answers
187 views

Get members of a given group from N:M relationship

I have this N:M relationship: CREATE TABLE auth_user ( id integer NOT NULL PRIMARY KEY, username character varying(150) NOT NULL UNIQUE ); CREATE TABLE auth_group ( id integer NOT NULL ...
3
votes
0answers
26 views

How to design a database for my entity-relationship diagram about Projects, Tasks and Developers

I'm new to database and my entity-relationship diagram is as follows: According to that I designed my SQL-DDL database statements as shown below: CREATE TABLE Developer ( DeveloperID int NOT ...
1
vote
0answers
114 views

3 table many-to-many relationship

I have 3 table Partcar BarbellWheels Assembly now, im confused how to manage this table to got connection, cause Partcar and Assembly is many-to-many relationship. But, BarbellWheels will merging ...
1
vote
2answers
60 views

Select Items that has one item but not the other

I have three tables: Table1: ID Name 1 Item1 2 Item2 3 Item3 Table2: ID Name Type 1 Logistics One 2 Human Resource Two 3 Finance ...
0
votes
1answer
48 views

Chained two Many-to-Many relations

I have the following case, which I can only describe as a chain of two many-to-many relationships: There are three business entities (tables) and two junction tables: Orders Products Features Orders ...
0
votes
2answers
36 views

How to link books of series together?

I have to link all the books in series together but I'm not sure. I have a SERIES_ID under which there is a name for a series, author, number of books and number book in the series. I'm not sure ...
0
votes
1answer
459 views

Building an bridge table from existing one with the use of the SELECT INTO

I have one big source table in which attributes are mixed with measures together. I want to make a bridge table from them to allow analysis of the data. Problem (in my case N is actually 22, ...
0
votes
1answer
52 views

Need help designing a query to pull a list of songs based on ranking and the last play date

I have a database of a list of songs used during a performance. The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best. The table is linked to tblEvents which ...
1
vote
0answers
111 views

Multiple many-to-many database relations

SO told me to ask here, so this is just copy-pasted from over there We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio ...
1
vote
1answer
307 views

Student and Course Relationship - Grade and Attendance

I was reading on this DBA page, about Student and Course relationship, and the use of a junction table. Suppose I wanted to do two things: Include the student grade for each course Allow the ...
9
votes
3answers
619 views

How to hint many-to-many join in SQL Server?

I have 3 "large" tables that join on a pair of columns (both ints). Table1 has ~200 million rows Table2 has ~1.5 million rows Table3 has ~6 million rows Each table has a clustered index on Key1, ...
2
votes
1answer
97 views

What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?

In mongoDB Many-to-Many Relationship Data Modeling by Mark Starkman, he tries to see how many-to-many relationship can be implemented in the MongoDB database. He suggests the following code will ...
1
vote
1answer
62 views

Find next entity with ordered junction table

I have guides which are linked to series (many to many) with a junction table like so: guides id etc.. (more columns) -- 1 13 21 30 series id etc.. (more columns) -- 1 3 4 series_guides (...
7
votes
5answers
938 views

Mutually exclusive many-to-many relationships

I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, ...
0
votes
1answer
46 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 ...
0
votes
1answer
756 views

Adding data in a bridge table automatically

Basically the concept is that a customer can place orders from multiple addresses and an address can have multiple customers, like different members of the same family. CREATE TABLE Customer ( ...
3
votes
3answers
108 views

Multiple Many-to-Many Relations

I've never come up against a similar situation and so I'm looking to gather some tips on how to accomplish this. Here's the situation: I'm creating an application to track and manage sheet music and ...
2
votes
1answer
53 views

How to constrain many-to-many to mark a single record as a “default”? [duplicate]

Suppose I have the following entities: customer(id, name) template(id, name) With a junction table: customer_template(id, customerId, templateId, is_default) A customer may have any number of ...
0
votes
1answer
23 views

Selecting union of results of dataset-producing function when applied element-wise to an array

This problem is somewhat an academic curiosity. I'm attempting to implement a SQL (i.e. not plpgSQL) function which takes an array of input data, transforms each entry to a set of zero or more rows ...
1
vote
1answer
124 views

SSIS: Slowly changing dimension with lists (one-to-many relationship)

Slowly changing dimension (specifically type 2) is a brilliant concept for being able to keep historical periodic (e.g. daily) data without the storage overhead of taking an entirely new snapshot of ...
2
votes
1answer
78 views

How can I represent a relationship of cardinality many to many?

I'm trying to design a database for the information shown in the table below: In this regard, let's make the following assumptions: Each Student may take many Courses Each Course may have many ...
2
votes
1answer
72 views

Approach to save many to many relationship in case select all values

I have Countries mapped to Products as many to many relationships. As a result a new mapping table is created with product_id and country_id. If a Product is saved then multiple country_ids are stored ...
1
vote
0answers
37 views

Reversing many-to-many relationships when transforming data into a galaxy schema

I'm building a galaxy schema for our analytics and sales data from our normalized data tables. I'm unsure how to treat many-to-many relationships in the facts tables. For example, for each sale, we ...
0
votes
2answers
2k views

How to implement many-to-many relation between three table?

I have these tables StudentInfo: subjectId(primary key) name location SubjectInfo: subjectId(primary key) subjectName totalmarks TeacherInfo: teacherId(primary key) teacherName ...
0
votes
1answer
77 views

MS Access Many-Many Query/Form Assistance

I have somewhat of a beginner-type question. Could someone look at the below relationships and let me know why I can't get the following form/query to only show the unique Appointment letters? I have ...
1
vote
1answer
42 views

Access articles from a table if user belongs to qualified group

The tables below are myuser & mygroup, where each user has membership to 1 or more groups via an M2M table membership. The articles are accessible by users if one of the condition is satisfied 1)...
7
votes
1answer
12k views

Mapping many-to-many relationship

I have two tables: Employee Table with columns employee_id (primary key) and employee_name. Company Table with columns company_id (primary key) and company_name. The companies allows its employee to ...
3
votes
1answer
408 views

Getting duplicate results in many to many query

I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the ...
3
votes
1answer
558 views

How to model a three-way association that involves Product, Category and Label?

I have the following three tables: products: product_id, product_name, ... categories: category_id, category_name, category_parent_id, category_priority (for sort ...
1
vote
1answer
216 views

UPDATE n:m relation in view as array (operations)

Environment I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The ...
3
votes
3answers
69 views

Meta-relationships (Relationships between relationships) versus tuples

I am designing a database which needs to store some fairly complex relationships among many different types of entities. In the simplest example, let us say I have three types of entities - "Student",...
1
vote
1answer
61 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, ...
2
votes
4answers
9k 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) ...
3
votes
2answers
52 views

Table with N associations

Suppose I have three tables: meta_tags, users, posts. (just an example) Users and Posts both could have tags, so my meta_tags table could be structured like meta_tags - user_id - post_id - ...
16
votes
1answer
3k views

Designing a database for a video game business domain with multiple many-to-many relationships

I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous ...
2
votes
1answer
1k views

Many to many between lots of tables?

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended? Each body type has its own table Each car in each body type table can has several features ...
2
votes
1answer
2k views

Unique key based on many-to-many pivot table

I have to manage artists and albums tables: | artists | | albums | | album_artist | +--------------+ +--------------+ +--------------+ | id | | id | | id ...