Questions tagged [many-to-many]
147 questions
16
votes
1 answer
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 many-to-many (M:N) relationships.
General scenario…
dpolaristar
- 193
- 1
- 11
15
votes
3 answers
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 must be either a Group or a Solo Performer (but not…
Boshir
- 151
- 1
- 3
14
votes
2 answers
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 called ProducerDetails that would have:
ProducerId…
Josh Darnell
- 30,133
- 5
- 70
- 124
11
votes
5 answers
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)
thing_id: uuid (fk)
primary key (container_id,…
antsyawn
- 265
- 1
- 3
- 7
9
votes
3 answers
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, Key2, and then one more column. Key1 has low…
Steven Hibble
- 760
- 5
- 10
9
votes
5 answers
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, animals or bacteria, and each plant, animal or…
Mad Scientist
- 1,260
- 14
- 22
9
votes
1 answer
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 work for other companies. So an employee can…
user2594
- 193
- 1
- 1
- 4
7
votes
1 answer
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 child categories, and many parent categories. Given…
dustytrash
- 171
- 5
7
votes
3 answers
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
---------
user_id
group_id
Now, I want to write a…
Eldamir
- 317
- 1
- 3
- 13
7
votes
3 answers
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(50))
productDescription (ntext)
productPrice…
user4321
- 215
- 1
- 3
- 9
6
votes
3 answers
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, but I would like to know why would he claim that?…
la_jankong
- 61
- 1
- 2
5
votes
3 answers
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() not null,
);
create table extra(
id uuid…
filipot
- 153
- 1
- 1
- 5
5
votes
1 answer
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
As you can see from my "schema", each car can…
Oscar
- 153
- 1
- 5
4
votes
3 answers
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:
user (user_id(pk), user_name)
…
xyz
- 257
- 1
- 2
- 11
4
votes
5 answers
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 PRIMARY KEY,
name character varying(80) NOT…
guettli
- 1,591
- 5
- 25
- 51