Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
27 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 ...
0
votes
2answers
17 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 ...
1
vote
1answer
26 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 ...
0
votes
0answers
19 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. ...
0
votes
1answer
34 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
1answer
27 views

Better solution for creating many-to-many table per entity

I have a table for images. I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images. I need to assign images to entities. The obvious solution is to ...
2
votes
2answers
57 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. ...
1
vote
3answers
50 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 ------...
2
votes
1answer
39 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-...
2
votes
1answer
43 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 ...
0
votes
1answer
45 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 ...
0
votes
0answers
14 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 ...
-1
votes
1answer
126 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. ...
0
votes
0answers
16 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" ...
0
votes
1answer
27 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 ...
1
vote
1answer
22 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
25 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
13 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
355 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
285 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
61 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
84 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
115 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 ...
2
votes
1answer
447 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
1answer
39 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
324 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
788 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
212 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
29 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
152 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
61 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
60 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
998 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
62 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
141 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
553 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
841 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
292 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
73 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 (...
8
votes
5answers
1k 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
48 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
1k 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 ( ...
2
votes
3answers
148 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
60 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
26 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
146 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 ...
1
vote
1answer
99 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
77 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
52 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 ...