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 these relationships are many to many, because a piece can be placed in many different toys, and a material can be associated with many different pieces. Here's where I lack the knowledge on implementation. There's another many to many relationship, where a toy can be made by many factories, and a factory can manufacture many types of toys. In the general case, for a particular toy, we want to know what pieces it has, and which materials are associated with those pieces. However, there's a case where the material can be different for a given piece *depending on the factory the toy is made in.* In short, there's a "default" case where we know that, for a given toy, we have a set of pieces, and for each of those pieces, we have a set of materials. But, for an arbitrary factory, the material associated with a piece is different. The furthest I've gotten is putting this information in the piece_material junction table: | piece_id | material_id | factory_id | |-----------|--------------|------------| | 1 | 1 | | | 1 | 2 | | | 1 | 3 | 1 | But this obviosuly doesn't work because I don't have information indicating which material is being replaced in a particular factory. I'm looking for a query that will get associated materials with a piece, and only the overrides if there are some present (i.e. in the last row of the above example, if it's overriding material 2, that would only get materials 1 and 3 back).

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 *

(id, first name, last name, gender, password, email)

*

(id, name)

*

(id, name, level, day, start time, end time)

## Attempted relationships (might be wrong) *

1->has->N Roles

(I think I need a user/roles bridging table) *

(user role 6) N->has(studies)->M Courses

*

(user role 3-5) N->has(teaches)->M Courses

## Example data

Table: Courses

id name    level    teacher   day start_time end_time students (0NF)
--+-------+--------+---------+---+----------+--------+--------------
1  English Beginner 1         Mo  12:00      13:00    101,102...
2  English Beginner 1         Tu  12:00      13:00    101,124...
3  English Beginner 1         Fr  13:00      14:00    101,105...
4  English Expert   1         We  14:00      15:00    145,155...
5  Maths   Single   2         Mo  12:00      13:00    135,163...
6  Maths   Single   3         Tu  13:00      14:00    192,123...
7  Maths   Full     3         Tu  15:00      16:00    134,101...

## Example report

Student                       (Id = 101)
-----------------------------

Attends English for Beginners (Taught by Teacher 1)
-----------------------------
Mon - 12:00 to 13:00
Tue - 12:00 to 13:00
Fri - 13:00 to 14:00

Attends Maths (Full)          (Taught by Teacher 3)
-----------------------------
Tue - 15:00 to 16:00

Please forgive me for any inconsistencies in structure - I am a beginner in DB design and DBA StackExchange also. Could I have the steps explained to arrive at a solution?

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 JPA, new rows are automatically inserted in the bridge table Order_Products. When in the Trigger I use

SET @numOfOPs = (
        SELECT COUNT(IDOrder) 
        FROM Order_Product 
        WHERE IDOrder = NEW.ID)

on new entries, the count on Order_Procut seems to return 0 (or the value before the insert on Order). Here the signature of the Trigger:

CREATE TRIGGER Order_AFTER_INSERT 
    AFTER INSERT ON Order 
    FOR EACH ROW

What could be the problem? I will add additional information if needed.

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 and topic have a many to many relationship. Problem: I am getting the topics that are assigned to other books because technically they are not assigned to my book. In depth look: I have a database full of books. A book can have many topics and a topic can have many books. I have create a user interface for adding a book. This interface shows all the topics available when the user choose let us say three topics three records are created in the books_has_topics table one field has an id for for the book the other for the topic. I also have a user interface for updating the book. I want the user to be able to change the topics. Code: this works it give me a list of all the assigned to the book based on the books id. SELECT * FROM topic T INNER JOIN book_has_topic BHT ON T.topic_id = BHT.topic_topic_id WHERE BHT.Book_book_id = [bookid]; This sort of works Not only do i get all the topics not assigned to the bookid I also get all the topics assigned to other books. SELECT DISTINCT topic_id, topic_name FROM topic T LEFT JOIN book_has_topic BHT ON T.topic_id = BHT.topic_topic_id WHERE BHT.book_book_id IS NULL or BHT.book_book_id [bookid];

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 have one fighter, one mage, and one healer? If we were simply dealing with binary relationship sets without key or total constraints, every instance of the relationship would be linked to an entity from each entity set. But in the case above, isn't it possible to not have one instance from a particular entity set? E.g. if the Party instance only had one fighter and one healer (and no mage)?

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 tables, but I am wondering if there is a better approach? My specific use case is relating invoices issued to payments received. I have an invoice fact table (each record designated with a unique InvoiceId) and a payment fact table (each record is designated with a unique PaymentId). My mapping table has columns for the InvoiceId and PaymentId as well as other useful information, but InvoiceId and PaymentId are not necessarily unique in their respective columns (because a payment can apply to multiple invoices and an invoice can be paid with multiple payments). Thanks in advance for any advice and suggestions!

I have product and documentation tables and want to hava a many-to-many relationship between them with an additional condition. The tables look like this:

create table product (
  id bigserial primary key
);

create table documentation (
  id bigserial primary key,
  type text not null
);

And condition is: each product can have any number of documentation but no more than 1 of each type. Is it possible to create such a constraint without addading a type column into the join table?

### 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 user records. * Therefore we must generate 50_000_000_000 user_item (50 billions) * We could potentially have even more * Won't be easy to shard nor partition because then, it will slow down any other operation (otherwise we need to scan everything) * Assume as query pattern (INSERT, SELECT, UPDATE) basic/typical m2m patterns (that could be found in any tutorial or example ### Question What's the best design or known solution for handling billions of Many to Many relationship in a database regardless of a schema? ### Schema Imagine this simple schema

CREATE DATABASE IF NOT EXISTS playground CHARACTER SET = latin1;
USE playground;

CREATE TABLE IF NOT EXISTS user
(
    id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255)    NOT NULL,
    PRIMARY KEY (id),
    INDEX user__name_fk (name)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

CREATE TABLE IF NOT EXISTS item
(
    id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255)    NOT NULL,
    PRIMARY KEY (id),
    INDEX user__name (name)
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

CREATE TABLE IF NOT EXISTS user_item
(
    user_id BIGINT UNSIGNED NOT NULL,
    item_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, item_id),
    INDEX user_item__item (item_id),

    FOREIGN KEY user_id_fk (user_id) REFERENCES user (id) ON DELETE CASCADE,
    FOREIGN KEY item_id_fk (item_id) REFERENCES item (id) ON DELETE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = latin1
  ROW_FORMAT = DYNAMIC;

-- create some default items
INSERT INTO item (name) VALUES ('item_1'), ('item_2'), ('item_3'), ('item_4'), ('item_5'), ('item_6'), ('item_7'), ('item_8'), ('item_9'), ('item_10');
-- create some users
INSERT INTO user (name) VALUES ('user_1'), ('user_2'), ('user_3'), ('user_4'), ('user_5'), ('user_6'), ('user_7'), ('user_8'), ('user_9'), ('user_10');
INSERT INTO user_item (user_id, item_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);

### More info I'm not asking on how to use many to many relation ship in MySQL, i know that. I'm asking what is the **more known** solution for a scaling issue, that is, when number of related records are **exponentially growing** to such big scale. Also I intentionally didn't add any query pattern (INSERT, SELECT, UPDATE) because **is irrelevant**. Assume the most/typical M2M pattern. I don't want to loose focus on the real question which is about **scaling** and huge amount of data. There must be some trick or some known workaround right? I'm also considering a NoSQL database so the answer could include anything non related to MySQL (or any SQL databases), I feel like this should be a common issue that many big company will face and hence there should be a common (or few) solution. The root cause of this issue is that, while MySQL is great to create relationship, it will grow **associative m2m table** exponentially. The 500 Millions x 100 == 50 Billions is just an example. But could theoretically happen. #### Clarification * I left query out in purpose because you can assume to most easy one. * I'm sure if I gave few example, will start to pop optimization over the specific query, that's not the question * I'm asking a very high level question, and if there is not a real known solution then a no with explaining why would suffice (assuming is correct) Here an example of a simple many to many query..

SELECT user.*, item.* FROM user
LEFT JOIN user_item ON user.id = user_item.user_id
LEFT JOIN item ON item.id = user_item.item_id
WHERE user.name = 'user_1';

Similar but not same questions * https://dba.stackexchange.com/questions/57284/best-design-for-a-manymany-relationship-issue * https://dba.stackexchange.com/questions/128275/design-a-many-to-many-table-for-scale * https://dba.stackexchange.com/questions/319353/many-many-to-many-relationships-databse-design-with-mysql * https://dba.stackexchange.com/questions/331724/better-database-design-for-nested-relation-many-to-many-to-many

I have a many to many relationship implemented: CREATE TABLE public.message ( id BIGSERIAL PRIMARY KEY, name varchar(40) UNIQUE NOT NULL ); CREATE TABLE public.package( id BIGSERIAL PRIMARY KEY, name varchar(40) UNIQUE NOT NULL ); CREATE TABLE public.package_to_message ( message_id BIGINT NOT NULL, package_id BIGINT NOT NULL, CONSTRAINT package_to_message_pk PRIMARY KEY (message_id, package_id) ); I need to select a package that has an association with a precisely defined set of messages [message_name_1, message_name_2, message_name_3]. With all of the above and none more. Is it possible to do this using a more or less optimized query? Nothing comes to mind.

I have a Course entity in my database and I want to represent its prerequisites. - A Course can have many prerequisites. - A Course can be a prerequisite for many other Courses - Clearly, this would be represented as a Many to Many relationship between the Course entity and itself. - But I also need to represent more complicated prerequisites that involve logical expressions. For example course1 might have the following prerequisites: course2 AND (course3 OR course4). I need to keep track of the expression and the order of the operands as well. One approach I thought about is to treat the expression as a product of sums. I would have a PrerequisiteGroup entity which represents a group of courses ORed together and then a Prerequisite many-to-many relationship to represent a group of PrerequisiteGroups ANDed together for every course. I feel this design might be too complicated when it comes to querying but I think it can represent any logical expression that only has AND and OR operators. Please let me know if there are better designs. Also, let me know if a relational database isn't the best to represent this, and if a graph-based database would be simpler.

I have a PostgreSQL 15 join table schema.m2mjointable to link records of several parent tables (schema.table_a, schema.table_b, ...) with other records of those same parent tables: | id | ref_table | ref_id | rep_table | rep_id | |---------:|-------------:|-------:|-------------:|-------:| | IDENTITY | TEXT | INT | TEXT | INT | |----------|--------------|--------|--------------|--------| | 1 | table_a | 1 | table_a | 2 | | 2 | table_a | 1 | table_c | 3 | | 3 | table_a | 4 | table_b | 7 | | 4 | table_b | 5 | table_a | 8 | | 5 | table_b | 9 | table_b | 11 | | 6 | table_c | 7 | table_b | 11 | In the parent tables (schema.table_a, schema.table_b, ...), there is always a boolean field named referenced which should tell if the record i of table n is at least referenced *once* by the pair of columns ref_table and ref_id in this join table. If that's the case it should be set to TRUE. It should be set to FALSE if it's never present in this pair of columns. How could I build a trigger function to update the referenced field of the parent tables with such trigger:

CREATE TRIGGER schema.set_referenced_state_biudt
  BEFORE INSERT OR UPDATE OR DELETE
  ON schema.m2mjointable
  FOR EACH ROW EXECUTE PROCEDURE schema.set_referenced_state();

BEGIN;
CREATE OR REPLACE FUNCTION schema.set_referenced_state()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
   -- set the BOOL value of the "referenced" field to TRUE or FALSE
   -- in the record id=value_of(ref_id) of the parent table=value_of(ref_table) 
   -- corresponding to those values of the record being currently inserted/updated/deleted
END;
$$;

I am setting up a database for a university and I'm struggling to setup sensible FOREIGN KEY CONSTRAINTS (and even tables) because I have two tables in which the PRIMARY KEYS of each can relate to multiple PRIMARY KEYS of the other table and vice versa: Concrete example: - I have a "users" table of all staff members where the primary ID is AUTO_INCREMENT - I have a "workgroups" table of all workgroups in the university where the primary ID is AUTO_INCREMENT - in the "users" table, there is a column called "workgroup(s)" which uses workgroups.id as a FOREIGN KEY - in the "workgroup" table, there is a column called "leader(s)" which uses users.id as a FOREIGN KEY my problem is that in rare cases, a person/user can be the leader of multiple workgroups AND in even rarer cases, a workgroup can have two leaders. I am reluctant to give the "users" table several "workgroup" columns (like "workgroup1", "workgroup2", etc) and equally reluctant to give the "workgroups" table several "leader" columns. And I believe there is no way to assign an array of user.ids to the workgroups.leader(s) column and have that be a FOREIGN KEY which actually references multiple user.ids, right? So, if users with IDs 10 and 15 are leaders of workgroup "genetics" (workgroups.id = 1), I cannot assign "workgroups.leaders @ id=1" to be [10,15] and have that actually be a FOREIGN KEY which references two user.ids correctly, right? I also have setup FOREIGN KEY CONSTRAINTS between the two columns "workgroups.leaders" and "users.id" in both directions going "ON DELETE SET NULL" and "ON UPDATE CASCADE". The intention being that if a user who is a workgroup leader is deleted, the workgroup should have NULL leaders until manually assigned some other user as leader and secondly: If a leader's user.id or a workgroups's id changes (both of which shouldn't happen), it would be updated in the other table, respectively. I assume that I have to setup these many-to-many relationships between "users.id" and "workgroup.leader(s)" in a third table?? So take out "leaders" column from "workgroups" table and take out "workgroup" column from "users" table? And then put them in a third table where a user can be linked to be leader of multiple workgroups AND have the FOREIGN KEYS set in this third table? Should I do this for all user workgroup relationships in a forth table? Because, aside from being leader, any user may be a MEMBER of several workgroups (without being its leader)... or could I put this all in a single third table with 4 columns like "relationship_id [INT, PRIMARY], user_id [REFERENCES users.id], workgroup_id [REFERENCES workgroups.id], is_leader [BOOL]"? Do I even need "relationship_id" here if this table is only used for joining on the two FOREIGN KEYS? I just hope that it'll never happen that a leader of one group can be in second group without being the second group's leader...

What is the correct sentence to express the relationship between the user and his direct manager: 1. A many-to-many table must be maintained because managers have several employees under them. 2. A field of type reference (foreign key) must be added to the users table with a reference to the record of his direct manager. 3. There is no need to add anything because a manager is also an employee, so we will use the inheritance mechanism. 4. Neither answer is correct. I think the 2nd option is the correct sentence because it suggests using a field of type reference (foreign key) in the users table to establish a direct relationship between a user and their manager

I'm no expert in DBMS and trying to build up **an alerting system that can attach itself to many tables**, so I figured asking the question would help my architecture. The thing that may confuse me a little is that you can have multiple alerts attached to **each model** (sessions, events, organization) that are represented in tables in my database. One record of each model can have multiple different alerts ringing up. The simplest way to solve this is to create a table such as

CREATE TABLE alerts (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_type character varying(50) NOT NULL,
    metadata jsonb,
    session_id uuid REFERENCES sessions(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    event_id uuid REFERENCES events(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    organization_id uuid REFERENCES organizations(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

But it seems to be a bad strategy if we grow that to many more tables attached to the alerting system. It obviously doesn't scale well. I'm hesitating between 2 strategies. You have a table alerts and to represent the many-to-many a second table alerts_relations that has table_name and table_id to make sense of each other model/table it's attached to.

CREATE TABLE alerts (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_type character varying(50) NOT NULL,
    metadata jsonb,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

CREATE TABLE alerts_relations (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_id uuid REFERENCES alerts(id) ON DELETE CASCADE ON UPDATE CASCADE,
    table_name character varying(50) NOT NULL,
    table_id character varying(50) NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

The other strategy removes table_name and table_id and replace it with the following

CREATE TABLE session_alerts (
    session_id uuid REFERENCES sessions(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

CREATE TABLE event_alerts (
    event_id uuid REFERENCES events(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

CREATE TABLE organization_alerts (
    organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

I'm wondering what strategy yields better performance and what's the easiest to maintain. If that helps, my codebase is in Golang, so I've to work through the SQL manually and do little ORM work. The last strategy seems to be the most convenient to my business logic because each relationship can be represented with a different table and name. Sounds easier to maintain, but I'm not sure about anything at this point; there may be drawbacks I don't see. Any other solution is welcome of course! Thanks for reading.

I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we have a mix of structured and unstructured data so initially decided to go with PostgreSQL and Mongo. We also have to restrict some data to some regions because of compliance and regulations but allow the rest of the data globally because of users moving around globally. Since we don't have anything critical like a bank, we are wondering if we just have to go with Mongo all the way as it makes our business logic, deployment and maintenance simple and fast. One issue is the many-many relations like user belongs to many groups and a group has many users. In relational, I could have a table with rows (group_id and user_id) and I can easily get all users in a group or all groups a user is in. In unstructured, I can store groups list in user's document and users list in group's document but now if a user joins a group, it becomes two updates (user's document and group's document). Same logic for user liking a post. Here we want all users who liked a post and all posts that the user liked. Even if one does not need one piece of information in real-time such as the app never shows the groups a user is in to the user, we might need it for data analysis which means we have to extract this from reading all groups' documents. We also feel PostgreSQL is cheaper when looking at AWS RDS vs MongoDB Atlas, so tempted to at least use it partially. Could anyone please let us know if there is an elegant solution for this or any blogs, posts that gives a solution? Regards, KSRD

I am in the conceptual design of a database, building tables and views, selecting the PKs and FKs (primary and foreign keys) and normalizing. Unfortunately, I am constrained to use Power Pivot on Excel to build the database, so where I would like to have a composite key I have to use an extra calculated column to generate a concatenation of columns as key.

Person (Name, DOB, Name-DOB [concatenated PK])
Name (Name [PK], Meaning, Origin)
DOB (DOB [PK], Astrological Sign)

I have a many-to-many recursive relationship that points from Person to Person, to map weddings. I need a linking table:

Wedding (Husband name, Husband DOB, Wife Name, Wife DOB)

Do I need a PK for the linking tables on the many-to-many relationships? If so, which? The only one I see (other than inventing a Serial Number for each row) would be a concatenation of the 4 attributes in the table, because any concatenation of 2 or 3 attributes has a chance to be non-unique. (Say someone can have a 2nd wedding after their 1st spouse dies.) But isn't a concatenation of 4 attributes too long? Do I need to normalize the linking tables on the many-to-many relationships? If so, how? 2NF (elimination of partial dependencies) and 3NF (elimination of transitive dependencies) are impossible since Husband DOB depends only on Husband Name (same for Wife). What FK do I use to link Wedding to Person? Should I use the Husband Name-Husband DOB and Wife Name-Wife DOB concatenations since Name-DOB is the PK of Person? If so, do I need to keep the individual attributes (Husband name, Husband DOB, Wife Name, Wife DOB) in Wedding since they are not needed in the many-to-many relationship? What problems will it pose in updating information into the database via user forms if those attributes are not there?

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 primary key default gen_random_uuid() not null,
);

create table car_extra_m2m(
    car_id uuid not null references car(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (car_id, extra_id)
);

create table person_extra_m2m(
    person_id uuid not null references person(id) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_id, extra_id)
);

Is it possible to express the two many to many tables in a single many to many table? Since we're using uuid the ids should never collide, so it might be possible to know the type just from the uuid? Like this pseudocode: (Edit: not valid syntax in postgresql, does there exist a valid syntax for this?)

create table extra_m2m(
    person_or_car_id uuid not null references (person(id) or car(id)) on delete cascade,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id)
);

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 column in junction table? Is it OK to normalization rules or is there a better solution?

I have this M2M join table:

CREATE TABLE [dbo].[RecipientsDonors]
(
  [RecipientId] [int] NOT NULL,
  [DonorId] [int] NOT NULL,
  CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED 
  (
    [RecipientId] ASC,
    [DonorId] ASC
  )
)

I also have these two indexes:

CREATE NONCLUSTERED INDEX [IX_RecipientsDonors_RecipientId] ON [dbo].[RecipientsDonors]
(
  [RecipientId] ASC
)

CREATE NONCLUSTERED INDEX [IX_RecipientsDonors_DonorId] ON [dbo].[RecipientsDonors]
(
  [DonorId] ASC
)

My intent with the two indexes is to speed single-column lookups. Given the existence of the primary key, are the indexes redundant? Or instead are they necessary because the primary key includes both columns?

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 is a PostgreSQL array[]::text[] type with zero-to-many urls in the array. It looks like {www.blah.com, www.blah2.com}. What I'm tryin to accomplish from a trigger on the tweet table is to create an entry in a urls_starting table and then adding the tweet/url_starting relationship in a tweet_x_url_starting. *Side note: The url_starting table is linked to a url_ending table* where the fully resolved url paths reside. The problem I face is deadlocks and I don't know what else to try. I went on an [Erwin Brandstetter](https://dba.stackexchange.com/users/3684/erwin-brandstetter) learning spree. (if you're out there man... THANK YOU! 💪) 1. [How to implement a many-to-many relationship in PostgreSQL?](https://stackoverflow.com/q/9789736/25197) 2. [Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING](https://dba.stackexchange.com/q/194756/60043) 3. [Postgres UPDATE … LIMIT 1 (skip locked help)](https://dba.stackexchange.com/q/69471/60043) I tried adding ORDER BY's for deterministic, stable orders and FOR UPDATE SKIP LOCKED but am not sure I'm doing any of it correctly. Here's the structure. **Using PostgreSQL 10.5**. CREATE TABLE tweet( id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY, twitter_id text NOT NULL, created_at timestamp NOT NULL, content text NOT NULL, urls text[], CONSTRAINT tweet_pk PRIMARY KEY (id) ); CREATE TABLE url_starting( id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY, url text NOT NULL, CONSTRAINT url_starting_pk PRIMARY KEY (id), CONSTRAINT url_starting_ak_1 UNIQUE (url) ); CREATE TABLE tweet_x_url_starting( id_tweet integer NOT NULL, id_url_starting integer NOT NULL, CONSTRAINT tweet_x_url_starting_pk PRIMARY KEY (id_tweet,id_url_starting) ALTER TABLE tweet_x_url_starting ADD CONSTRAINT tweet_fk FOREIGN KEY (id_tweet) REFERENCES tweet (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE tweet_x_url_starting ADD CONSTRAINT url_starting_fk FOREIGN KEY (id_url_starting) REFERENCES url_starting (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE; Here's the tweet table trigger. CREATE TRIGGER create_tweet_relationships AFTER INSERT OR UPDATE ON tweet FOR EACH ROW EXECUTE PROCEDURE create_tweet_relationships(); And finally, the function. CREATE FUNCTION create_tweet_relationships () RETURNS trigger LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 1 AS $$ BEGIN IF (NEW.urls IS NOT NULL) AND cardinality(NEW.urls::TEXT[]) > 0 THEN WITH tmp_url AS ( INSERT INTO url_starting (url) SELECT UNNEST(NEW.urls) ORDER BY 1 ON CONFLICT (url) DO UPDATE SET url = EXCLUDED.url RETURNING id ) INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting) SELECT NEW.id, id FROM tmp_url ORDER BY 1, 2 FOR UPDATE SKIP LOCKED ON CONFLICT DO NOTHING; END IF; RETURN NULL; END $$; I blindly threw the stuff I read about into the function without success. The error looks like this. deadlock detected DETAIL: Process 11281 waits for ShareLock on transaction 1317; blocked by process 11278. Process 11278 waits for ShareLock on transaction 1316; blocked by process 11281. HINT: See server log for query details. CONTEXT: while inserting index tuple (494,33) in relation "url_starting" SQL statement "WITH tmp_url AS ( INSERT INTO url_starting (url) SELECT UNNEST(NEW.urls) ORDER BY 1 ON CONFLICT (url) DO UPDATE SET url = EXCLUDED.url RETURNING id ) INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting) SELECT NEW.id, id FROM tmp_url ORDER BY 1, 2 FOR UPDATE SKIP LOCKED ON CONFLICT DO NOTHING" PL/pgSQL function create_tweet_relationships() line 12 at SQL statement Error causing transaction rollback (deadlocks, serialization failures, etc). How can I stop the deadlocks? Thanks! 👍