Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
0
votes
0answers
13 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
16 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
24 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
12 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
171 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
98 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
55 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
75 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
293 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
233 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
445 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
189 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
126 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
50 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
646 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
61 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
124 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
394 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
695 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
138 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
70 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
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
47 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
864 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
118 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
56 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
24 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
135 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
86 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
73 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
44 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
86 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
15k 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
556 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
620 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
222 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
70 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
70 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, ...
3
votes
4answers
10k 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 ...
