init.sql

CREATE TABLE users (
    id                      SERIAL NOT NULL PRIMARY KEY, /* SERIAL is denoted as INTEGER when referencing */
    email                   VARCHAR(64) NOT NULL UNIQUE,
    nickname                VARCHAR(16) NOT NULL UNIQUE,
    password                VARCHAR(64) NOT NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TYPE part_type AS ENUM ('ETC', 'CPU', 'GPU', 'MB', 'SSD', 'HDD');

CREATE TABLE parts (
    id                      SERIAL NOT NULL PRIMARY KEY,
    name                    VARCHAR(64) NOT NULL UNIQUE,
    type                    part_type NOT NULL,
    image_url               VARCHAR(256) NOT NULL DEFAULT '/static/imgs/path/to/placeholder.png',
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE combinations (
    id                      SERIAL NOT NULL PRIMARY KEY,
    owner_id                INTEGER
                            CONSTRAINT combination_owner_id REFERENCES "users",
    name                    VARCHAR(64) NOT NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE relations (
    id                      SERIAL NOT NULL PRIMARY KEY,
    combination_id          INTEGER NOT NULL
                            CONSTRAINT relation_combination_id REFERENCES "combinations",
    part_id                 INTEGER
                            CONSTRAINT relation_part_id REFERENCES "parts"
);

CREATE TABLE part_info_cpu (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT cpu_part_id REFERENCES "parts",
    family_type             VARCHAR(32),
    socket_type             VARCHAR(32),
    core_count              VARCHAR(32),
    thread_count            VARCHAR(32),
    base_clock              VARCHAR(32),
    max_clock               VARCHAR(32),
    mem_type                VARCHAR(32),
    tdp                     VARCHAR(32)
);

CREATE TABLE part_info_gpu (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT gpu_part_id REFERENCES "parts",
    chipset_manufacturer    VARCHAR(32),
    family_type             VARCHAR(32),
    chipset                 VARCHAR(32),
    vram_type               VARCHAR(32),
    vram_size               VARCHAR(32),
    interface               VARCHAR(32),
    max_monitor_count       VARCHAR(32),
    power_consumption       VARCHAR(32)
);

CREATE TABLE part_info_mb (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT mb_part_id REFERENCES "parts",
    board_type              VARCHAR(32),
    cpu_socket              VARCHAR(32),
    cpu_chipset             VARCHAR(32),
    power_phase             VARCHAR(32),
    ram_type                VARCHAR(32),
    ram_speed               VARCHAR(32),
    ram_slot_count          VARCHAR(32),
    form_factor             VARCHAR(32)
);

CREATE TABLE part_info_ram (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT ram_part_id REFERENCES "parts",
    usage_type              VARCHAR(32),
    form_factor             VARCHAR(32),
    size                    VARCHAR(32),
    generation              VARCHAR(32),
    base_clock              VARCHAR(32),
    package_count           VARCHAR(32)
);

CREATE TABLE part_info_ssd (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT ssd_part_id REFERENCES "parts",
    interface               VARCHAR(32),
    size                    VARCHAR(32),
    form_factor             VARCHAR(32),
    nand_type               VARCHAR(32),
    dram_type_size          VARCHAR(32),
    protocol                VARCHAR(32)
);

CREATE TABLE part_info_hdd (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT hdd_part_id REFERENCES "parts",
    usage_type              VARCHAR(32),
    disk_standard_size      VARCHAR(32),
    interface               VARCHAR(32),
    buffer_size             VARCHAR(32),
    rpm                     VARCHAR(32),
    max_speed               VARCHAR(32),
    access_method           VARCHAR(32)
);

CREATE TABLE part_info_etc (
    part_id                 INTEGER NOT NULL
                            CONSTRAINT etc_part_id REFERENCES "parts",
    tags                    JSONB NOT NULL DEFAULT '{}'::jsonb
);