Fiddle: https://dbfiddle.uk/-JLFuIrN ## Table

CREATE TABLE files (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);

CREATE TABLE folders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);

CREATE TABLE file_folders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  file_id bigint NOT NULL REFERENCES files,
  folder_id bigint NOT NULL REFERENCES folders,
  UNIQUE (file_id, folder_id)
);

## Query

/*
  Merges
*/

WITH targets AS (
  SELECT 
    ARRAY (
      SELECT
        id
      FROM
        folders TABLESAMPLE BERNOULLI (50)
      LIMIT 3
    ) AS folders
),
-- basically a setup to ensure unique target/folder pairs
-- and no targets in the merges
input_folders AS (
  SELECT
    folders.id AS folder_id,
    random_array_element(targets.folders) AS target_id
  FROM
    folders
    CROSS JOIN
    targets
  WHERE
    NOT ( 
      folders.id = ANY (targets.folders)
    ) 
),
input_files AS (
  SELECT
    file_folders.id,
    file_folders.folder_id,
    file_folders.file_id,
    input_folders.target_id
  FROM
    input_folders
    INNER JOIN
    file_folders
    ON
      input_folders.folder_id = file_folders.folder_id
      OR
      input_folders.target_id = file_folders.folder_id
),
deleted_files AS (
    WITH deletions AS (
    SELECT
      inputs.id
    FROM
      input_files AS inputs
      INNER JOIN
      input_files AS targets
      ON
        NOT (inputs.folder_id = targets.target_id)
        AND
        inputs.file_id = targets.file_id
  )
  DELETE
  FROM
    file_folders
  WHERE
    id IN (
      SELECT
        id
      FROM
        deletions
    )
),
merged_files AS (
  WITH merges AS (
    SELECT
      inputs.id,
      inputs.folder_id,
      inputs.target_id
    FROM
      input_files AS inputs
      INNER JOIN
      input_files AS targets
      ON
        NOT (inputs.folder_id = targets.target_id)
        AND
        NOT (inputs.file_id = targets.file_id)
  )
  UPDATE file_folders
  SET
    folder_id = merges.target_id
  FROM
    merges
  WHERE
    merges.id = file_folders.id
),
deleted_folders AS (
  DELETE
  FROM
    folders
  WHERE
    id IN (
      SELECT DISTINCT
        folder_id
      FROM
        input_folders
    )
)
SELECT
  folders AS targets
FROM
  targets
;

## Inputs The array-transforming setup is me trying to replicate the JSON input of the application in pure SQL. The input looks like this:

interface IQueryInput extends Array {};

interface IMergeInput {
  target: IEntityID;
  inputs: IEntityID[];
};

// postgresql bigints are treated as strings in the application
type IEntityID = string;

So the prepping query from above can be replaced with:

WITH inputs AS (
  SELECT
    input.*
  FROM
    -- the application interpolates JSON there
    json_to_recordset($inputs$$inputs$) AS input(
      target bigint,
      inputs bigint[]
    )
),
input_folders AS (
  SELECT
    inputs.target AS target_id,
    merge.folder_id
  FROM
    inputs
    CROSS JOIN
    UNNEST(inputs.inputs) AS merge(
      folder_id
    )
)

It must run as a batch operation, so the application provides these guaranties for the query input: - all target values are unique. - all inputs concatenated result in unique values. - target values do not intersect with concatenated inputs. Therefore input_folders always ends up as unique target_id-folder_id pairs. The query is ran as a background task, so the speed and memory are of secondary importance. The main requirement is of a typical transaction: either it should go through completely on success or reject completely on any error. ## The problem I want to "merge" several folders into a single folder. So given a target_id and an array of folder_ids, replace all foreign references to folder_ids with target_id and remove non-target folder afterwards.
This however becomes an issue in relations table with unique constraints, since after updating the references there are duplicates.
So I went this path: 1. Select all relation rows related to the query, so all file_folders with target_ids and folder_ids in them. 2. Separate them into two categories: - Deletes - the rows which will result in dupes when updated. - Merges - the rows which will not result in. 3. Delete the delete candidates. 4. Update the merge candidates. 5. Repeat previous 4 steps for all relations. 6. Delete rows in folders with folder_ids. However I still stumble upon unique key violation error.
"Merge" is in quotes because it doesn't look like what I am trying to do can be accomplished by [merge in docs](https://www.postgresql.org/docs/15/sql-merge.html) and it requires a newer version of postgresql anyway.

I've never come across this situation, not sure how to go about it. Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and the Asset tables. A portfolio can have either Asset 1, or Asset 2, or both, or none. Can I do something like the bridge table in the image above, i.e. where the bridge table (example below) would contain in the same column, the foreign keys to two different tables

I am creating a database table setup for user preferences in my application based partially on this design and while I created the tables and foreign keys, I can't seem to figure out the relationships of these tables. The attached screenshot is the DB design and below is what I currently believe are the correct relationships user to user_preference = one-to-many user to preference = many-to-many user_preference to user = many-to-one user_preference to preference = many-to-one user_preference to allowed_preference_value = many-to-one preference to allowed_preference_value = one-to-many

I need to design an ER diagram, but I am confused in a section, more specifically how to relate the post with the groups, because I need that post contains the information of in which group I publish and that user I publish it, I have two diagrams one where the relation of post, group and user are direct, and other where the post is related by means of the table generated of the relation many to many between group and user. My question is which of these two is more recommended. Diagram A Diagram B

I've seen this post (How should I model an “either/or” relationship? ) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship. Say I have an entity MACHINE, and I want to creat a relationship to connect it with another entity OS, call it "installs" or whatsoever. And this OS has 2 subtypes: WINDOWS and MAC *(Linux and Unix also work but just for demo purpose they are not included)*. Not considering virtual machine or double OS, I can only choose one of these 2 subtypes of OS, how should I model this in the entity-relationship model? Should I - Create 1 relationship between MACHINE and OS. Or - Create 2 relationship between MACHINE and WINDOWS, MACHINE and MAC. Or - Create 1 ternary relationship between MACHINE, WINDOWS and MAC. And should I add additional attributes to the entities or the relationship? Thanks:D

I am facing an issue while creating a DB (in MariaDB) for a university research group. I am currently designing which tables I need for this. The goal is to be able to put a web interface on top of this DB in the future where the leader (professor) of the group can log in and see all his staff, students, publications, etc. and change stuff (kick out a student, for example) while the other scientists in the group can do different things there. For example: A student should only be able to edit his stuff (email, name, etc) or a senior scientist should be able to assign students to projects, etc. My question now come from the way I handle the "people" table(s). My first idea was to make a table for "staff" (people that get paid and have a contract with the university) and one for "students" (people that do not have a contract, maybe are just guests or just there for a few months for a thesis, etc). Now the problem is: It happens quite often that former students later get hired by the professor and therefore (in this DB design) should move from the "students" table to the "staff" table. However, this would destroy (unlink?) all their relations that they had in the "students" table to other tables (like for example "publications" table for the publications they published while they were still a student). Is there any way in MySQL to move an entry from one table to another and automatically (or reasonable easily by hand) update all their relations to any other tables in the database? Concrete example: Student Benjamin with "student_id" 75 moves from "students" table to "staff" table and gets "staff_id" 188 there. Is there a way to automatically replace "students.student_id 75" with "staff.staff_id 188" throughout the entire database to keep his old relations alive? Should he even be MOVED from "students" to "staff" table or rather be kept in the "students" table (with an "inactive" flag or something) and just get copied to the "staff" table with a new ID? That would make him redundant in the DB, right? Or would it make more sense to create just one "persons" table and manage their status via a "role" column or "persons_to_roles" link table? Ideally, I wanted to have separate tables for "students" and "staff", so I can give the web apps that the students use only write access to the "students" table and not the "staff" one. Thanks for any help!

I'm trying to insert a dataset to two tables (a and b) while trying to keep correlation between the rows (b.id_a). Does anybody have any idea how to set foreign key (id_a) in "inserted_b"?

BEGIN;
CREATE TABLE a(
	id SERIAL PRIMARY KEY,
	col_a INT
);
CREATE TABLE b(
	id SERIAL PRIMARY KEY,
	id_a INTEGER REFERENCES a(id),
	col_b INT
);
WITH
input(idx,col_a,col_b) AS (
	VALUES (0, 0, NULL), (1, 1, 0)
),
inserted_a AS (
	INSERT INTO a(col_a)
	SELECT col_a FROM input
	RETURNING *
),
inserted_b AS (
	INSERT INTO b(col_b)
	SELECT col_b FROM input
	WHERE input.col_b IS NOT NULL
	RETURNING *
)
SELECT *
FROM a
LEFT JOIN b ON b.id_a = a.id;
ROLLBACK;

I was thinking about returning that extra generated "idx" column within _inserted_a_, but i can't use RETURNING for anything that had not been inserted.

Me and my friend are in the process of creating an ER diagram for a professional soccer league, we are both very new to Database so we aren't sure if everything we do is correct. When creating a relationship between a manager and a club, he made this: I'm quite confused about why the 2 relationships managedBy and signsWith are Many to Many. My friend said that multiple managers can manage multiple clubs, one-on-one at a time. But shouldn't that be One to One instead? Since at any moment in time, a club can only have a single manager managing it and a manager can only have a single club to manage (we do not care about assistant manager in this instance); same thing about the signsWith relationship. So which one of us is right?

I have an unchangable user table which stores user data, depending on the role(A 1-M connection with the roles table) the user could be either an establishment worker or an establisment owner. I need to add the establishment, where the establishment could have a single owner but many workers. What's the Ideal solution for this issue?

Let's say I have table Account that contains username and password of users to login:-

account (
    account_id BIGINT PRIMARY KEY,
    username VARCHAR(30),
    password VARCHAR(30)
)

-- I have two more tables that stores their credentials in the above table:-

customer (
    delivery_address VARCHAR(100),
    account_id FOREIGN KEY,
    ..30 more customer columns
)

admin (
    sp_admin_code INT,
    account_id FOREIGN KEY,
    ..30 more admin columns
)

An admin cannot be a customer and use same username and password to login as a customer. My Questions:- 1. Is this the proper way to implement this kind of relationship? If not, what is? 2. Do I need to add some constraint in the primary key of the account table to denote that it's single entry can either be referenced by customer table or by admin table at once? 3. Now we also know that, an entry on account can only exist if there is an entry for it on either customer table or admin table and vice versa. So do we need to or if we want do add some cascade/constraint, how do we do it or how should we do it or if we should do it at all? sorry im confused.. thank you!

Recently I started discovering a topic around "How to design db schema for storing structures similar to Instagram/Facebook/Reddit comments?". After extensive research, I was able to find a bunch of different answers on SO, SE, medium articles and etc. Meanwhile, all of these articles were pretty basic and always point out a Closure table pattern , which I used once back in the day. I did implement a comments/replies system only once a few years ago using PostgreSQL and since then the product is already not in production, so I don't know how my solution would scale in a data-intensive environment. Therefore, I decided to ask a specific question with specific requirements and constraints, so I could probably get a hint from someone who had this experience in production! Here we go with two different tasks: **Task 1** *Requirements:* - When I open a post I see only the first level of comments. In particular, 50 most liked comments are ordered ascending by the number of likes. - For every top-level comment: if a comment has only one reply - display this reply too. - For every top-level comment: if a comment has multiple replies, display only the one which is the most liked. - When the user clicks on "more replies": Display replies in descending order by their created_datetime. - The max depth is 2: Only the top-level comment and replies to it can exist. Replies to lower-level comments(depth == 0) should always be displayed near their parents. The only thing that distinguishes them is just a mention of a user you reply to, like @ on instagram. *Questions(only related to the design of relational database with Closure table):* - What are the problems you faced in **production** with it and how you had to fix them? What would you recommend to people who just start with this, what should they spend their time on at the beginning to prevent a cascade of mess in the future? - Is there a better pattern with RDBS nowadays for this purpose? Let's imagine the system grows. We don't talk about thousands of requests, but we talk about hundreds of thousands of comments and replies to them. E.g. some celebrity posted a message and then all the fans started replying, having conversations and etc. It results in a lot of rows in our records in both the comment and closure tables. Our queries to group by amount of likes start getting much slower on some posts, causing long-running transactions which cause a ton of mess and even probably downtimes. Again, that's what it looks to me that could happen if we just use a closure table. But what really happens? Curious to hear stories of people who had problems with it in really data-intensive applications. E.g. We can shard the table somehow, right? Or for really big posts we could cache a lot of stuff, right? **Task 2** - The main difference to the first one: When I open a post I see 50 most liked comments but with all their children. Meaning I fetch the whole tree for these 50 first comments. Depth is not limited. *Questions(only related to the design of relational database with Closure table):* - Should we simplify the logic and become less ambitious, so we would go with business requirements similar to the ones in task 1? (when we don't have infinite depth and comments trees can grow only in width) I assume otherwise this is almost impossible to scale such a business logic when there are millions or billions of comments. - If the answer to the first question is no, how the magic happens then? ( I don't believe that such product requirements could be scalable while infrastructure would still stay profitable; costs would grow exponentially imo) **General questions to be answered first:** - Is a relational database still a case for such a problem nowadays? I don't know much about graph databases, but wouldn't it be optimal to store such hierarchical data there? Probably I just need to discover graph databases deeper, so please feel free to link the related articles. Doesn't seem I found them in a week, so I would definitely need help with finding the right materials :) **To sum up:** I understand that my questions may seem pretty vague, but they are also quite complex and require the knowledge of someone who had this experience. Meanwhile, I am also quite opinionated on some topics (like growth of costs/sharding/caching) and that's why it is even more difficult for me to compile the opinion - I wanna have more thoughts gathered, not only mine. In case you think an extensive answer would take too much of your time - please give me just short answers like yes or no and just link all the resources you think could really help me to build my opinion on this topic. Sharing your real production experience of working with such systems would be really helpful and appreciated! Thanks!

I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair. The way I'm achieving this right now is by iterating through every combination and comparing columns based on their distinct data. the pseudocode is something like this: for(int i=0;i