Skip to main content
19 votes
Accepted

Build JSON object from one-to-many relationship data in a single query?

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries ...
Erwin Brandstetter's user avatar
18 votes
Accepted

Designing a database for a video game business domain with multiple many-to-many relationships

Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. ...
MDCCL's user avatar
  • 8,520
16 votes
Accepted

Mapping many-to-many relationship

You will have both employee table and company table to store employee and company info. But you need another table for the relation since it is a many-to-many relationship. Also here, the work hours ...
ukll's user avatar
  • 781
10 votes

Mutually exclusive many-to-many relationships

There is a way to implement this declaratively only without changing your current setup much, if you agree to introduce some redundancy to it. What follows can be considered a development on RDFozz's ...
Andriy M's user avatar
  • 23.1k
9 votes
Accepted

Using JSONB Column or Another Table to Save Relationsips

Document types like json or jsonb (or xml or hstore) are convenient to store, well, documents. Ideal for data with varying keys and rarely updated and not-too-complex filter criteria in queries. ...
Erwin Brandstetter's user avatar
8 votes
Accepted

Get members of a given group from N:M relationship

This EXISTS query returns unique users, regardless of whether there can be duplicate entries in auth_user_groups. SELECT * FROM auth_user u WHERE EXISTS ( SELECT 1 FROM auth_user_groups ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Many to many between lots of tables?

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for ...
Serg's user avatar
  • 1,266
6 votes
Accepted

Are individual indexes necessary on columns that are included in a compound primary key?

An index can always be used by just a leading key. So the IX_RecipientsDonors_RecipientId index is not really necessary, as the primary key already covers the same columns. While it will slightly ...
Charlieface's user avatar
  • 13.3k
5 votes

How to model a three-way association that involves Product, Category and Label?

Note: This answer presents an approach to cover specifically the business rules derived by way of the earliest series of comment and chat interactions (most of which can be seen in this question ...
MDCCL's user avatar
  • 8,520
5 votes
Accepted

Unique key based on many-to-many pivot table

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key. There are two variations of this approach. First, you can keep the ...
Andriy M's user avatar
  • 23.1k
5 votes
Accepted

How to hint many-to-many join in SQL Server?

Just to be clear, the optimizer already knows that it's a many-to-many join. If you force merge joins and look at an estimated plan you can see a property for the join operator which tells you if the ...
Joe Obbish's user avatar
  • 32.5k
5 votes
Accepted

Efficiently return two aggregated arrays from a m:n table

Query Creating a temporary table and looping are expensive overkill for the purpose. You don't even need plpgsql in the first place - though it may be slightly faster for repeated calls in the same ...
Erwin Brandstetter's user avatar
5 votes

Recursivly get a Tree Via self joined table

Recursive CTEs are notoriously hard to learn. Take a look at this fiddle to play with a working model. The 2nd query in the CTE, or Common Table Expression, needs to reference the first query. This ...
Hannah Vernon's user avatar
  • 70.2k
5 votes

Many to Many - Concept

You'll need a third linking table (junction table) as you mentioned but it should solely consist of the keys between the two tables being linked to reduce redundancy and improve normalization. So you ...
J.D.'s user avatar
  • 38.1k
4 votes

Query "all of" across many-to-many relation

You mentioned in a later comment: I have some situations where it will produce upwards of 50 joins That many joins are a game changer. Better base query Normally, the order of joins is mostly ...
Erwin Brandstetter's user avatar
4 votes

Would it be correct to add extra columns to the junction table according to normalization rules?

yes you can add columns to a bridge table, as long as they are attributes to that for that relationship. Customers CustomerProducts Products customerId customerId productId ...
nbk's user avatar
  • 8,434
4 votes
Accepted

Single foreign key for referencing one of multiple tables

Yea there's no reason not to if it fits your use case better, there's nothing wrong with that design. My only suggestion would to add a extra_type field to explicitly identify whether the record is of ...
J.D.'s user avatar
  • 38.1k
3 votes

Mutually exclusive many-to-many relationships

If you only need 2 or 3 categories (plants/metazoa/bacteria) and you want to model an XOR relationship, maybe an "arc" is the solution for you. Advantage: no need for triggers. Example diagrams can ...
stefan's user avatar
  • 2,507
3 votes

Mutually exclusive many-to-many relationships

One option is add a containertype_id to the Container table. Make the column NOT NULL, and a foreign key to a ContainerType table, which would have entries for each type of item that can go in a ...
RDFozz's user avatar
  • 11.6k
3 votes

What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?

You can add a third collection, named person_group, being described in that blog picture. db.person_groups.insert({ "_id": ObjectId("5e54ed9f48dc5922c0094a42"), "groupId": ObjectId("...
Marcos Zolnowski's user avatar
3 votes
Accepted

Multiple Many-to-Many Relations

I think your solution (two join tables) is valid. However, I suspect that you are tracking two different things, and wanted to be sure that you were clear on this in terms of design. The artist(s) ...
RDFozz's user avatar
  • 11.6k
3 votes
Accepted

How to constrain many-to-many to mark a single record as a "default"?

You could use a filtered unique index. create unique nonclustered index ixf_customer_template_default on dbo.customer_template(customerId,is_default) where is_default = 1; That will allow ...
SqlZim's user avatar
  • 2,420
3 votes
Accepted

Select Items that has one item but not the other

You can use NOT EXISTS to eliminate rows linked to undesirable types (like @a_horse already suggested). But since multiple rows can match any row in table1 also use EXISTS to qualify rows linked to ...
Erwin Brandstetter's user avatar
3 votes

M:M Table Design - Use of PK

Here's the canonical design for a Many-to-Many linking table. You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a ...
David Browne - Microsoft's user avatar
3 votes
Accepted

How to prevent deadlocks in many-to-many insert/update trigger function?

This ultimately boiled down to 2 things. Concurrent writes will eventually deadlock if the inserted data isn't sorted before inserting. Within my trigger function all inserts were sorted but there ...
GollyJer's user avatar
  • 381
3 votes
Accepted

How to select rows which are mapped to a junction table in a many to many relationship?

Assuming that locations and professionals also have their PK columns as location_id and professional_id, respectively, and location_professional uses those columns to link the two entities: SELECT ...
Rick James's user avatar
  • 78.6k

Only top scored, non community-wiki answers of a minimum length are eligible