Questions tagged [many-to-many]
The many-to-many tag has no usage guidance.
127
questions
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, ...
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 ...
- 11
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 ...
- 11
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 ...
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 ...
- 25
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. ...
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 |
+-...
- 101
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 ...
- 101
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'...
- 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 ...
- 101
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() ...
- 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 ...
- 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 ...
- 19
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 ...
- 11
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: ...
- 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 ...
- 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 ...
- 11
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 ...
- 11
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 ...
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 ...
- 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 ...
- 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 | ...
- 101
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 ...
- 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 ...
- 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....
- 115
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 ...
- 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,
...
- 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-...
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 ...
- 151
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 ...
- 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 ...
- 67
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 ...
- 101
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 ...
- 103
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 ...
- 103
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 ...
- 111
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 ...
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 ...
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 ...
- 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. ...
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 ...
- 11
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 ...
- 23
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
------...
- 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-...
- 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 ...
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
...
- 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 ...
- 1
-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"...
- 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" ...
- 131
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
...
- 101
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),...