Which Postgres schema should I go with?

This is my first time working with databases. I’m designing a database for an e-commerce project. I ended up with two versions of the schema, and I don’t know which one to go with or what its pros and cons are.

V1:

-- Person Schema
create table person
(
    id      serial primary key,
    name    text    not null,
    email   text    null,
    phone   text    not null,
    address text    null,
    balance integer not null default 0
);

-- Product Schema
create table product_category
(
    id        serial primary key,
    name      text not null,
    parent_id integer references product_category (id)
);

create table product_price
(
    id              serial primary key,
    suggested_price integer not null,
    wholesale_price integer not null,
    lowest_price    integer not null
);

create table product_stock
(
    id            serial primary key,
    current_stock integer not null,
    warning_stock integer not null
);

create table product
(
    id       serial primary key,
    name     text    not null,
    cost     integer not null,
    image    text    not null,

    category integer references product_category (id),
    price    integer references product_price (id) unique,
    stock    integer references product_stock (id) unique,
    supplier integer references person (id)
);

V2:

-- Person Schema
create table person
(
    id      serial primary key,
    name    text    not null,
    email   text    null,
    phone   text    not null,
    address text    null,
    balance integer not null default 0
);

-- Product Schema
create table product_category
(
    id         serial primary key,
    name       text not null,
    parent_id  integer references product_category (id),
    product_id integer references product (id)
);

create table product_price
(
    id              serial primary key,
    suggested_price integer not null,
    wholesale_price integer not null,
    lowest_price    integer not null,
    product_id      integer references product (id) unique
);

create table product_stock
(
    id            serial primary key,
    current_stock integer not null,
    warning_stock integer not null,
    product_id    integer references product (id) unique
);

create table product
(
    id       serial primary key,
    name     text    not null,
    cost     integer not null,
    image    text    not null,

    supplier integer references person (id)
);

The first schema relies on storing foreign keys inside the main table product while version 2 does not. Which version is better and why?

You need to sign in to view this answers

Related Post