Define database schema

[sir-geronimo]

Context

A database schema will allow us to model and define our relationships.

Models:

NOTE Fields with ? means they're optional (nullable). Syntax is model_name (...fields).

Models

• project (id, name, description, author_id, created_at, updated_at, ?deleted_at)
• issue (id, name, ?description, color, author_id, status_id, ?parent_id, created_at, updated_at, ?deleted_at)
• author (id, name, email, created_at, updated_at, ?deleted_at)
• comment (id, content, author_id, ?parent_id, created_at, updated_at, ?deleted_at)
• issue_status (id, name, color, description, created_at, updated_at, ?deleted_at)

Pivoting tables:

• issue_status_history (id, issue_id, status_id, ?previous_status_id, created_at, updated_at, ?deleted_at)
• issue_comment (id, issue_id, comment_id)
• project_issue (id, project_id, issue_id)

Action

• Use prisma.io for the schema definition.
• Use prisma's postgresql connector.
• Create a table for each of the models mentioned above.

[soworksl2]

issue_status need issue_id

[sir-geronimo]

The issue model has a status_id field to identify its status.

The initial idea was that issue_status would be a collection of user-defined statuses (like columns or swim lanes).

Different examples for different use cases:

Writer

1. draft
2. in-progress (writing)
3. pending-publisher
4. done
5. on-sale

Software engineering

1. open
2. in-progress
3. in-review
4. testing
5. done

But now I think it'd be a good idea and simpler for the user to get started with a set of predefined statuses that they could modify to their own needs and liking: draft, open, in-progress, in-review, done, closed,

The issue could keep track of its status. We could create a pivot table for keeping a history of all the statuses for an issue.

For example:

issue_status_history (id, issue_id, status_id, ?previous_status_id, created_at, updated_at, ?deleted_at)

[soworksl2]

I guess that the table issue_comment is not necessary. because an issue yes can contains multiple comments but a don't think a comment should have multiples issue.

we can just use issue_comment.issue_id just unique but could be better just add issue_id to the comment table.

look a diagram of how it could be.

I am ready to commit, but let me know about this topic first

[sir-geronimo]

You're right.

We could also drop the project_issue table, because an issue would only be part of a project.

My idea was to have a single table for all the comments and have separate tables to link comments with resources, such as project_comment, issue_comment, and others like post_comment, etc.

[reon150]

Why does issue_status_history have the columns updated_at and deleted_at? The records in this table should not be updated or deleted since it is historical. Also, why does it need a previous_status_id if it can be sorted by the creation date column?🤔 Wouldn't it also be good to put an author_id to determine who updated the status?

[soworksl2]

Why does issue_status_history have the columns updated_at and deleted_at? The records in this table should not be updated or deleted since it is historical. Also, why does it need a previous_status_id if it can be sorted by the creation date column?🤔 Wouldn't it also be good to put an author_id to determine who updated the status?

About updated_at and deleted_at I completely agree with you, the issue_status_history should not be deleted or updated because it is historical. the database design has some inconsistency that should be fixed.

about the previous_status_id How I understand it is that it do not serve to order the status in the history. instead all the status in the history of a issue are saved into a linked-list like data-structure of one direction, and the previous_status_id serve to connect the last status with the previous one and connect it to the respectively history. but thinking it well the first status in the history need the issue_id anyway.

this way, it has more stability in my opinion but that is true that it add more data to the database, and complexity

about the author_id I agree, that is a fantastic Idea

I think that the design of the db should be rethought again, I know the structure and the intrinsic meaning of the database I can make the respectively changes and publish the last version here, with types and attributes. and all the details can be discussed in our discord server.

let to redesign this database slowly and without rush.

[sir-geronimo]

@reon150

Also, why does it need a previous_status_id if it can be sorted by the creation date column?

I think these entries shouldn't be modified or deleted. We can proceed to remove them. 👍

Wouldn't it also be good to put an author_id to determine who updated the status?

This is actually a very good suggestion, and I think we could do better and create a history for all the changes performed on an issue, such as updating status, changing description, etc. But let's keep it simple for now and only add the author_id to the issue_status_history model.

@soworksl2

You're very right about the linked-list structure for the status history. We can determine the last status of an issue either by querying and sorting all the statuses of a specific issue and selecting the last one (most recent).

but thinking it well the first status in the history need the issue_id anyway.

this way, it has more stability in my opinion but that is true that it add more data to the database, and complexity

All the entries for the issue_status_history need a issue_id, that's their index. I just noticed you added the @unique, but it can't be unique, what could be unique is the combination of issue_id and status_id, but not only one.

What do you mean by adds more complexity?

[soworksl2]

@sir-geronimo

All the entries for the issue_status_history need a issue_id, that's their index. I just noticed you added the @unique, but it can't be unique, what could be unique is the combination of issue_id and status_id, but not only one.

Yes, my mistake, sorry for the error.

What do you mean by adds more complexity?

I mean the Issue already has an status_id that define its current status. then the issue_status_history only serve to save the history of all status that an issue has been set, the order can be specified by the created_at field in issue_status_history. then the previous_status_id only serve to add more reliability to the order and say where this status comes from perfectly.

If we need the last or first status in the history, order the table by created_at and get the first or last entry respectively.

but if you wanna keep it, there is no problem.

[Edit] I also found another complication that is just a little picky. the previous_status_id should be UNIQUE or @unique in prisma, otherwise an item in the linked-list could contain 2 elements. e.g:

status-1 <-- status-2 <-- status-3 ____^--- status-4

the 4th and 3th status in the history can link to the 2th.

The problem comes when it also need to accept NULL, because the first element in history will have no previous.

that is true that Postgress support that but not all DB do, that is why prisma does not support this either (Or at least I am wrong, tell me please) -> Prisma @unique and nullable issue

The first workaround that comes to me is to make it not unique, but then the next field generated by prisma ORM as a consecuense of previous should be a list and not a single optional value.

What to do? let next as a list and prevent repetition from code?