Database Schema ADR

[pietrop]

Is your Pull Request request related to another issue in this repository ?

https://github.com/orgs/bbc/projects/33#card-22923922

Describe what the PR does

moved database schema ADR to this repo from client repo.

State whether the PR is ready for review or whether it needs extra work

Work in progress to discuss options

Additional context

NA

[allishultes]

~The user.email and user_paper_edits.id are meant to correspond as foreign keys, but user.email is a text field and user_paper_editds.id is a numeric field.~

See below for summary.

[allishultes]

~There is also currently an issue where annotations.transcript_id is set as a unique field; however, I don't think each annotation needs to belong to a different transcript.~

~Similarly: user_projects.user_id is also set as a unique value (but more than one project should be able to belong to the same person)?~

See below for summary.

[allishultes]

~There was a spelling error in paper_edits.description - fixed in my PR but not the DB schema.~

See below for summary.

[allishultes]

Summary of issues found when investigating database spike:

• The user.email anduser_paper_edits.id are meant to correspond as foreign keys, but user.email is a text field and user_paper_editds.id is a numeric field.

• If a table's primary key is set to an auto-incrementing serial, it cannot correspond to a foreign key set as having a numeric value — numeric has to be set to integer. This occurred in a number of instances (I may have missed some):

  • user_paper_edits.paper_edit_id => Paper_edits.id
  • user_projects.project_id => Projects.id
  • Paper_edits.project_id => Projects.id
  • Media.transcript_id => Transcripts.id
  • Transcripts.project_id => Projects.id
  • Annotations.label_id => Labels.id
  • Annotations.transcript_id => Transcripts.id
  • user_transcripts.transcript_id => Transcripts.id

• There is a spelling error in paper_edits.description (description is spelled wrong).

• Currently, some fields are set as needing a unique value that do not seem as if they need to have a unique value. annotations.transcript_id is set to have a unique value; however, I don't think each annotation needs to belong to a different transcript. Similarly, user_projects.user_id is set as a unique value (but more than one project should be able to belong to the same person?). There are probably more that James and I did not catch.

• There's inconsistent capitalisation in the tables right now - it would be lovely for the neurotic editors among us to keep this the same across all ten!

In order to run the Sql commands, we needed to add the foreign key constraints that are exported from the scheme to the create table commands themselves, rather than adding them as alter table statements at the bottom of the file. We are currently unsure whether, having fixed the issues above, these would have worked as-exported or not.

Example of changes:

CREATE TABLE IF NOT EXISTS "Annotations" (
    "id" serial NOT NULL UNIQUE,
    "user_id" TEXT NOT NULL,
    "transcript_id" integer NOT NULL UNIQUE,
    "label_id" integer NOT NULL,
    "time_start" numeric NOT NULL,
    "time_end" numeric NOT NULL,
    "description" TEXT NOT NULL,
    "created_at" timestamp with time zone NOT NULL,
    "updated_at" timestamp with time zone NOT NULL,
    CONSTRAINT "Annotations_pk" PRIMARY KEY ("id"),
    CONSTRAINT "Annotations_fk0" FOREIGN KEY ("user_id") REFERENCES "Users"("email"),
    CONSTRAINT "Annotations_fk1" FOREIGN KEY ("transcript_id") REFERENCES "Transcripts"("id"),
    CONSTRAINT "Annotations_fk2" FOREIGN KEY ("label_id") REFERENCES "Labels"("id")
) WITH (
  OIDS=FALSE
);

versus:

CREATE TABLE "Annotations" (
    "id" serial NOT NULL UNIQUE,
    "user_id" TEXT NOT NULL,
    "transcript_id" serial NOT NULL UNIQUE,
    "label_id" numeric NOT NULL,
    "time_start" numeric NOT NULL,
    "time_end" numeric NOT NULL,
    "description" TEXT NOT NULL,
    "created_at" timestamp with time zone NOT NULL,
    "updated_at" timestamp with time zone NOT NULL,
    CONSTRAINT "Annotations_pk" PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);
...
ALTER TABLE "Annotations" ADD CONSTRAINT "Annotations_fk0" FOREIGN KEY ("user_id") REFERENCES "Users"("email");
ALTER TABLE "Annotations" ADD CONSTRAINT "Annotations_fk1" FOREIGN KEY ("transcript_id") REFERENCES "Transcripts"("id");
ALTER TABLE "Annotations" ADD CONSTRAINT "Annotations_fk2" FOREIGN KEY ("label_id") REFERENCES "Labels"("id");