Xata•2mo ago

Links (Relationships) database structure and filter / query approach

Hi everyone, I am very new to Xata and databases generally and I am trying to build a small web app. I am using Next.js Latest, typescript. I am looking for some guidance on setup and then filtering the data, so any help would be appreciated: The situation I have is: sub tasks (Many) -> Task (Many) -> milestone (Many) - Project (many) - Team / organisation (One) The Table structure I thought of: - Sub_task (many-to-one) -- Link to task.id - Task (Many-to-one) -- Link to Milestone.id - Milestone (many-to-one) -- Link to Project.id - Project (Many-to-one) -- Link to Organisation.id - Organisation (one) Question: Is the above overkill or am on the right path? The follow up would be then I would need to filter and display the sub tasks against the task, then against the milestone etc etc, as these fields are dynamic I am not sure how to build the query for this either which is efficient and will allow me to display the data in a grid format where all the sub tasks will display under the respective task etc etc. I have created the picture to try and help my thought process. Thank you in advanced for any help or suggestions.

3 Replies

cmck•2mo ago

Q: Is the above overkill or am on the right path? A: It's best to avoid lots of nested hierarchical relations, so I would try to reduce the number of one-to-many relations if possible. Performance will be impacted as you'll have to perform a lot of joins over many rows to get all the data you want. If you don't have many orgs, projects, etc. then this mightn't be an issue, but even 3 orgs with 3 projects, 3 milestones, 5 tasks, 3 subtasks each produces 54675 subtasks. Q: You can test your schema and queries using xata itself (or a local pg installation with pg admin). Make sure your tables have primary keys. Below is a sample schema in xata and a query that aggregates all the data by organisation. Sample schema (be sure to click on "Schema & migration" after creation to adapt it and add the xata columns)

CREATE TABLE "organisations" (
  "org_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "org_name" text
);

CREATE TABLE "projects" (
  "projects_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "project_name" text,
  "org_xata_id" text
);

CREATE TABLE "milestones" (
  "milestones_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "milestone_name" text,
  "project_xata_id" text
);

CREATE TABLE "tasks" (
  "tasks_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "task_name" text,
  "milestone_xata_id" text
);

CREATE TABLE "subtasks" (
  "subtasks_id" integer PRIMARY key,
  "xata_id" text UNIQUE NOT NULL,
  "subtask_name" text,
  "parent_task_xata_id" text
);

ALTER TABLE "projects" ADD FOREIGN KEY ("org_xata_id") REFERENCES "organisations" ("xata_id");

ALTER TABLE "milestones" ADD FOREIGN KEY ("project_xata_id") REFERENCES "projects" ("xata_id");

ALTER TABLE "tasks" ADD FOREIGN KEY ("milestone_xata_id") REFERENCES "milestones" ("xata_id");

ALTER TABLE "subtasks" ADD FOREIGN KEY ("parent_task_xata_id") REFERENCES "tasks" ("xata_id");

CREATE TABLE "organisations" (
  "org_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "org_name" text
);

CREATE TABLE "projects" (
  "projects_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "project_name" text,
  "org_xata_id" text
);

CREATE TABLE "milestones" (
  "milestones_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "milestone_name" text,
  "project_xata_id" text
);

CREATE TABLE "tasks" (
  "tasks_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "task_name" text,
  "milestone_xata_id" text
);

CREATE TABLE "subtasks" (
  "subtasks_id" integer PRIMARY key,
  "xata_id" text UNIQUE NOT NULL,
  "subtask_name" text,
  "parent_task_xata_id" text
);

ALTER TABLE "projects" ADD FOREIGN KEY ("org_xata_id") REFERENCES "organisations" ("xata_id");

ALTER TABLE "milestones" ADD FOREIGN KEY ("project_xata_id") REFERENCES "projects" ("xata_id");

ALTER TABLE "tasks" ADD FOREIGN KEY ("milestone_xata_id") REFERENCES "milestones" ("xata_id");

ALTER TABLE "subtasks" ADD FOREIGN KEY ("parent_task_xata_id") REFERENCES "tasks" ("xata_id");

And the query:

SELECT 
    o."org_name",
    p."project_name",
    ARRAY_AGG(
        DISTINCT m."milestone_name"
    ) AS milestones,
    ARRAY_AGG(
        DISTINCT t."task_name"
    ) AS tasks,
    ARRAY_AGG(
        DISTINCT s."subtask_name"
    ) AS subtasks
FROM 
    "organisations" o
JOIN 
    "projects" p ON p."org_xata_id" = o."xata_id"
LEFT JOIN 
    "milestones" m ON m."project_xata_id" = p."xata_id"
LEFT JOIN 
    "tasks" t ON t."milestone_xata_id" = m."xata_id"
LEFT JOIN 
    "subtasks" s ON s."parent_task_xata_id" = t."xata_id"
WHERE 
    o."xata_id" = 'xata_2'  -- Replace 'xata_1' with the specific org_xata_id
GROUP BY 
    o."org_name", p."project_name"
ORDER BY 
    p."project_name";

SELECT 
    o."org_name",
    p."project_name",
    ARRAY_AGG(
        DISTINCT m."milestone_name"
    ) AS milestones,
    ARRAY_AGG(
        DISTINCT t."task_name"
    ) AS tasks,
    ARRAY_AGG(
        DISTINCT s."subtask_name"
    ) AS subtasks
FROM 
    "organisations" o
JOIN 
    "projects" p ON p."org_xata_id" = o."xata_id"
LEFT JOIN 
    "milestones" m ON m."project_xata_id" = p."xata_id"
LEFT JOIN 
    "tasks" t ON t."milestone_xata_id" = m."xata_id"
LEFT JOIN 
    "subtasks" s ON s."parent_task_xata_id" = t."xata_id"
WHERE 
    o."xata_id" = 'xata_2'  -- Replace 'xata_1' with the specific org_xata_id
GROUP BY 
    o."org_name", p."project_name"
ORDER BY 
    p."project_name";

almac2017OP•2mo ago

Thank you for your feedback, I really appreciate the help and especially the query guidance. In this type of scenario what is the best practice approach for the table structure? would it be more json fields in a table to consolidate the sub-tasks against each task?

cmck•2mo ago

A json column is a good place to start. You could just have orgs and projects and put all the project information (milestones, tasks, subtasks) in a json column. If you really need to query any of the subtask, task, or milestone tables by themselves or with joins (tasks & subtasks, for example), then having a table for them makes sense. Otherwise, it's probably wiser to keep the info in one place. You might also consider a nosql database if you really need a deep hierarchical structure of data.

Gaming

Programming

Links (Relationships) database structure and filter / query approach