Skip to main content

Questions tagged [many-to-many]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
16 votes
1 answer
7k 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 ...
dpolaristar's user avatar
15 votes
3 answers
9k views

Modeling a scenario in which each Music Artist is either a Group or a Solo Performer

I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below. Scenario description An Artist has a Name, and ...
Boshir's user avatar
  • 151
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 ...
Josh Darnell's user avatar
  • 29.4k
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) ...
antsyawn's user avatar
  • 255
9 votes
5 answers
3k 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, ...
Mad Scientist's user avatar
9 votes
1 answer
41k 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 ...
user2594's user avatar
  • 193
9 votes
3 answers
2k 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, ...
Steven Hibble's user avatar
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 ------...
Eldamir's user avatar
  • 297
7 votes
3 answers
8k views

SQL - Many-to-Many relationship with same table and with relationship constraint

I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns: id (serial, pk) productName (nvarchar(...
user4321's user avatar
  • 215
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
6 votes
3 answers
4k views

Why would I need a third table in a one-to-many relationship?

Good day, Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, ...
la_jankong's user avatar
5 votes
1 answer
4k 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 ...
Oscar's user avatar
  • 153
4 votes
5 answers
655 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 ...
guettli's user avatar
  • 1,531
4 votes
3 answers
1k views

How to achieve many to many relationship in this database design?

I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist. What I have tried so far is: ...
xyz's user avatar
  • 257
4 votes
1 answer
4k views

How to make unique check without creating heavy index in postgres

I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
ramusus's user avatar
  • 143
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() ...
filipot's user avatar
  • 143
4 votes
1 answer
6k 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 ...
user197675's user avatar
4 votes
2 answers
3k views

Many-to-many relationship when one entity is without attributes

I'm interesting in mapping many-to-many relationship to relational model. I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-...
Kobe-Wan Kenobi's user avatar
4 votes
0 answers
2k views

Postgres: Arrays instead of intermediate tables for many-to-many relations

I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex ...
sudo's user avatar
  • 1,011
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: ...
Hamid Sajjadi's user avatar
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 ( [...
InteXX's user avatar
  • 559
3 votes
1 answer
3k 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 ordering)...
yemet's user avatar
  • 333
3 votes
1 answer
2k views

Is this acceptable practice for a many to many?

As a newbie in database design, I have a question about many-to-many relationship practice. This question is actually a follow up question of this question, but I found it too big to explain in a ...
Vahx's user avatar
  • 225
3 votes
1 answer
2k 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 ...
user1338998's user avatar
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 ...
GabrielF's user avatar
  • 161
3 votes
2 answers
709 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
3 votes
3 answers
110 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",...
alexw's user avatar
  • 192
3 votes
2 answers
6k views

Three-sided many-to-many relationship design

I have 3 entity that have a relation to each other. My entities are: OPTION PLAN PRICE Relationships: PLAN and OPTION have a many-to-many relationship Map of PLAN and OPTION has a many-to-many ...
iman's user avatar
  • 131
3 votes
1 answer
3k 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 ...
Alexis's user avatar
  • 43
3 votes
1 answer
2k views

One-to-many relationship on a “‘junction’ table”

I have two entity's - Workout and Exercise. These will have a 'many-to-many' (M:N) relationship through the junction table WorkoutExercise. Things start to get complicated when I need to have a 'one-...
mrmustard's user avatar
3 votes
0 answers
197 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 ...
Gamma's user avatar
  • 131
3 votes
1 answer
1k views

Three tables joined by a many-to-many relationship?

I'm working on creating a db model for the following requirements: I have many Types that have many different Statuses. Depending on the Type and Status, the Status can have certain SubStatuses. So ...
JAck28's user avatar
  • 133
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 ...
GollyJer's user avatar
  • 381
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 ...
Russell Ormes's user avatar
2 votes
3 answers
2k 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 ...
Michael's user avatar
  • 123
2 votes
1 answer
1k 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 ...
Izzy's user avatar
  • 135
2 votes
2 answers
133 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 ...
The Godfather's user avatar
2 votes
1 answer
199 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 ...
ShooShoSha's user avatar
2 votes
1 answer
108 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 ...
Tom O'Connell's user avatar
2 votes
1 answer
2k 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 ...
rostamiani's user avatar
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 ...
bguernouti's user avatar
2 votes
1 answer
197 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 ...
Himanshu Bhandari's user avatar
2 votes
0 answers
129 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
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 ...
buzzysin's user avatar
  • 121
2 votes
2 answers
2k 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 ...
Son Nguyen's user avatar
2 votes
1 answer
136 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-...
Kroka's user avatar
  • 121
2 votes
2 answers
55 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 - ...
SoWizardly's user avatar
2 votes
1 answer
417 views

Design a many-to-many table for scale [closed]

I am thinking about scaling issues with many-to-many join tables. I have a constructed example as following: Many users have many wishlists. On every wishlist are many items. If I scale this table ...
John Smith's user avatar
1 vote
4 answers
117 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 ...
Federico Baù's user avatar
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 ...
Fırat Kaya's user avatar