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" <[email protected]> написал:

…

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 <#2 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AI1grt1DOg-bo7Ob3kl7wktYf9cHlFb-ks5sl4OtgaJpZM4PdeJw> .