DEV Community

Lance Pollard
Lance Pollard

Posted on

The only PostgreSQL schema you need, for Graph data.

Here is basically what I'm going to use.

SET client_encoding = 'UTF8';

CREATE TABLE link_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
);

CREATE TABLE base_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code BOOLEAN
);

CREATE TABLE code_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code BYTEA
);

CREATE TABLE date_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code DATE
);

CREATE TABLE fork_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code NUMERIC(8,2)
);

CREATE TABLE form_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code INTEGER
);

CREATE TABLE line_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code CHARACTER VARYING(255)
);

CREATE TABLE load_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code JSON
);

CREATE TABLE race_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code TIMESTAMP WITH TIME ZONE
);

CREATE TABLE text_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code TEXT
);

CREATE TABLE time_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code time without time zone
);

CREATE TABLE tune_list (
  mark BIGSERIAL PRIMARY KEY NOT NULL,
  home BIGINT,
  name CHARACTER VARYING(255),
  code REAL
);

CREATE INDEX base_code_index ON base_list USING btree (code);
CREATE INDEX base_home_name_index ON base_list USING btree (home, name);
CREATE INDEX code_code_index ON code_list USING btree (code);
CREATE INDEX code_home_name_index ON code_list USING btree (home, name);
CREATE INDEX date_code_index ON date_list USING btree (code);
CREATE INDEX date_home_name_index ON date_list USING btree (home, name);
CREATE INDEX fork_home_name_index ON fork_list USING btree (home, name);
CREATE INDEX form_code_index ON form_list USING btree (code);
CREATE INDEX form_home_name_index ON form_list USING btree (home, name);
CREATE INDEX line_code_index ON line_list USING btree (code);
CREATE INDEX line_home_name_index ON line_list USING btree (home, name);
CREATE INDEX link_code_index ON link_list USING btree (code);
CREATE INDEX link_home_name_index ON link_list USING btree (home, name);
CREATE INDEX load_home_name_index ON load_list USING btree (home, name);
CREATE INDEX race_code_index ON race_list USING btree (code);
CREATE INDEX race_home_name_index ON race_list USING btree (home, name);
CREATE INDEX text_home_name_index ON text_list USING btree (home, name);
CREATE INDEX time_code_index ON time_list USING btree (code);
CREATE INDEX time_home_name_index ON time_list USING btree (home, name);
CREATE INDEX tune_home_name_index ON tune_list USING btree (home, name);
Enter fullscreen mode Exit fullscreen mode

One table per datatype, essentially, and one table for links. Everything has a name. BigInteger sequences for the IDs so they can grow pretty large. The queries are the hard part, trying to figure out how to serialize your standard JSON records from something like this.

Top comments (0)