database schema draft

[epogrebnyak]

[epogrebnyak]

Image above is from http://dbdesigner.net/designer/schema/112953

[epogrebnyak]

Here is Postgres code from dbdesigner:

CREATE TABLE "User" (
    "uid" serial NOT NULL,
    "name" varchar NOT NULL UNIQUE,
    CONSTRAINT User_pk PRIMARY KEY ("uid")
) WITH (
  OIDS=FALSE
);

CREATE TABLE "Repo" (
    "rid" serial NOT NULL,
    "name" varchar NOT NULL,
    "owner_id" varchar NOT NULL,
    CONSTRAINT Repo_pk PRIMARY KEY ("rid")
) WITH (
  OIDS=FALSE
);

CREATE TABLE "Issue" (
    "iid" serial NOT NULL,
    "number" int NOT NULL,
    "repo_id" int NOT NULL UNIQUE,
    "minutes" int NOT NULL DEFAULT '0',
    "rate" double NOT NULL DEFAULT '0',
    "is_open" bool NOT NULL DEFAULT '0',
    CONSTRAINT Issue_pk PRIMARY KEY ("iid")
) WITH (
  OIDS=FALSE
);

CREATE TABLE "Contract" (
    "cid" int NOT NULL,
    "freelancer_id" int NOT NULL,
    "issue_id" int NOT NULL,
    "when_started" DATE NOT NULL UNIQUE,
    "when_ended" DATE NOT NULL UNIQUE,
    CONSTRAINT Contract_pk PRIMARY KEY ("cid")
) WITH (
  OIDS=FALSE
);

ALTER TABLE "Repo" ADD CONSTRAINT "Repo_fk0" FOREIGN KEY ("owner_id") REFERENCES "User"("uid");

ALTER TABLE "Issue" ADD CONSTRAINT "Issue_fk0" FOREIGN KEY ("repo_id") REFERENCES "Repo"("rid");

ALTER TABLE "Contract" ADD CONSTRAINT "Contract_fk0" FOREIGN KEY ("freelancer_id") REFERENCES "User"("uid");
ALTER TABLE "Contract" ADD CONSTRAINT "Contract_fk1" FOREIGN KEY ("issue_id") REFERENCES "Issue"("iid");

[epogrebnyak]

Here is a similar code in PonyORM https://editor.ponyorm.com/user/epony78/issue_broker/designer:

CREATE TABLE "user" (
  "id" SERIAL CONSTRAINT "pk_user" PRIMARY KEY,
  "username" TEXT NOT NULL,
  "classtype" TEXT NOT NULL
);

CREATE TABLE "repo" (
  "id" SERIAL CONSTRAINT "pk_repo" PRIMARY KEY,
  "name" TEXT NOT NULL,
  "project_owner" INTEGER NOT NULL
);

CREATE INDEX "idx_repo__project_owner" ON "repo" ("project_owner");

ALTER TABLE "repo" ADD CONSTRAINT "fk_repo__project_owner" FOREIGN KEY ("project_owner") REFERENCES "user" ("id");

CREATE TABLE "issue" (
  "id" SERIAL CONSTRAINT "pk_issue" PRIMARY KEY,
  "repo" INTEGER NOT NULL,
  "minutes" INTEGER,
  "rate" DOUBLE PRECISION,
  "is_open" BOOLEAN,
  "is_closed_by_owner" BOOLEAN
);

CREATE INDEX "idx_issue__repo" ON "issue" ("repo");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__repo" FOREIGN KEY ("repo") REFERENCES "repo" ("id");

CREATE TABLE "contract" (
  "id" SERIAL CONSTRAINT "pk_contract" PRIMARY KEY,
  "issue" INTEGER NOT NULL,
  "freelancer" INTEGER NOT NULL,
  "when_open" TIMESTAMP,
  "when_closed" TIMESTAMP
);

CREATE INDEX "idx_contract__freelancer" ON "contract" ("freelancer");

CREATE INDEX "idx_contract__issue" ON "contract" ("issue");

ALTER TABLE "contract" ADD CONSTRAINT "fk_contract__freelancer" FOREIGN KEY ("freelancer") REFERENCES "user" ("id");

ALTER TABLE "contract" ADD CONSTRAINT "fk_contract__issue" FOREIGN KEY ("issue") REFERENCES "issue" ("id")

[dyakubovsky]

ER Disign issue-broker ver.1 : https://editor.ponyorm.com/user/epony78/issue_broker/designer

CREATE TABLE "issue_state" (
  "id" SERIAL CONSTRAINT "pk_issue_state" PRIMARY KEY,
  "name" TEXT NOT NULL,
  "priority" TEXT NOT NULL
);

CREATE TABLE "issue_type" (
  "id" SERIAL CONSTRAINT "pk_issue_type" PRIMARY KEY,
  "name_type" TEXT NOT NULL
);

CREATE TABLE "priority" (
  "id" SERIAL CONSTRAINT "pk_priority" PRIMARY KEY,
  "name" TEXT NOT NULL,
  "level" TEXT NOT NULL
);

CREATE TABLE "users" (
  "id" SERIAL CONSTRAINT "pk_users" PRIMARY KEY,
  "username" TEXT NOT NULL,
  "passwd" BYTEA NOT NULL,
  "email" TEXT NOT NULL,
  "type_of_user" BOOLEAN NOT NULL,
  "github_profile" TEXT NOT NULL
);

CREATE TABLE "contract" (
  "id" SERIAL CONSTRAINT "pk_contract" PRIMARY KEY,
  "title" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "create_date" TIMESTAMP,
  "closed_date" TIMESTAMP,
  "freelancer" INTEGER NOT NULL
);

CREATE INDEX "idx_contract__freelancer" ON "contract" ("freelancer");

ALTER TABLE "contract" ADD CONSTRAINT "fk_contract__freelancer" FOREIGN KEY ("freelancer") REFERENCES "users" ("id");

CREATE TABLE "repo" (
  "id" SERIAL CONSTRAINT "pk_repo" PRIMARY KEY,
  "name" TEXT NOT NULL,
  "users" INTEGER
);

CREATE INDEX "idx_repo__users" ON "repo" ("users");

ALTER TABLE "repo" ADD CONSTRAINT "fk_repo__users" FOREIGN KEY ("users") REFERENCES "users" ("id");

CREATE TABLE "issue" (
  "id" SERIAL CONSTRAINT "pk_issue" PRIMARY KEY,
  "issue_type" INTEGER,
  "priority" INTEGER NOT NULL,
  "contract" INTEGER,
  "summury" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "estimate" INTEGER,
  "rate" DOUBLE PRECISION,
  "issue_state" INTEGER NOT NULL,
  "create_date" TIMESTAMP NOT NULL,
  "close_date" TIMESTAMP,
  "solve_date" TIMESTAMP,
  "attachment" BYTEA,
  "reporter" INTEGER NOT NULL,
  "repo" INTEGER,
  "github_pull_request" TEXT NOT NULL,
  "assigner" INTEGER
);

CREATE INDEX "idx_issue__assigner" ON "issue" ("assigner");

CREATE INDEX "idx_issue__contract" ON "issue" ("contract");

CREATE INDEX "idx_issue__issue_state" ON "issue" ("issue_state");

CREATE INDEX "idx_issue__issue_type" ON "issue" ("issue_type");

CREATE INDEX "idx_issue__priority" ON "issue" ("priority");

CREATE INDEX "idx_issue__repo" ON "issue" ("repo");

CREATE INDEX "idx_issue__reporter" ON "issue" ("reporter");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__assigner" FOREIGN KEY ("assigner") REFERENCES "users" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__contract" FOREIGN KEY ("contract") REFERENCES "contract" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__issue_state" FOREIGN KEY ("issue_state") REFERENCES "issue_state" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__issue_type" FOREIGN KEY ("issue_type") REFERENCES "issue_type" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__priority" FOREIGN KEY ("priority") REFERENCES "priority" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__repo" FOREIGN KEY ("repo") REFERENCES "repo" ("id");

ALTER TABLE "issue" ADD CONSTRAINT "fk_issue__reporter" FOREIGN KEY ("reporter") REFERENCES "users" ("id")

[dyakubovsky]

I've made A Design more simple than ver1. : https://editor.ponyorm.com/user/epony78/issue_broker/designer any recomendation?

[epogrebnyak]

Does not render on mobile, but I'll check soon

25 сент. 2017 г. 13:42 пользователь "PayProtect" notifications@github.com написал:

I've made A Design more simple than ver1. : https://editor.ponyorm.com/user/epony78/issue_broker/designer any recomendation?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/epogrebnyak/issue-broker/issues/2#issuecomment-331844117, or mute the thread https://github.com/notifications/unsubscribe-auth/AI1grt1DOg-bo7Ob3kl7wktYf9cHlFb-ks5sl4OtgaJpZM4PdeJw .