schema.sql

\c products;
CREATE TABLE products (
  product_id INT GENERATED BY DEFAULT AS IDENTITY,
  product_name TEXT,
  slogan TEXT,
  product_description TEXT,
  category TEXT,
  default_price TEXT,
  PRIMARY KEY (product_id)
);

CREATE TABLE styles (
  style_id INT GENERATED BY DEFAULT AS IDENTITY,
  product_id INT,
  style_name TEXT,
  original_price TEXT,
  sale_price TEXT,
  default_style BOOLEAN,
  PRIMARY KEY(style_id),
  CONSTRAINT fk_product_id
    FOREIGN KEY(product_id)
      REFERENCES products(product_id)
);

CREATE TABLE features (
  feature_id INT GENERATED BY DEFAULT AS IDENTITY,
  product_id INT,
  feature_name TEXT,
  feature_value TEXT,
  PRIMARY KEY(feature_id),
  CONSTRAINT fk_product_id
    FOREIGN KEY(product_id)
      REFERENCES products(product_id)
);

CREATE TABLE related_products (
  relation_id INT GENERATED BY DEFAULT AS IDENTITY,
  current_product_id INT,
  related_product_id INT,
  PRIMARY KEY(relation_id),
  CONSTRAINT fk_current_product_id
    FOREIGN KEY(current_product_id)
      REFERENCES products(product_id),
  CONSTRAINT fk_related_product_id
    FOREIGN KEY(related_product_id)
      REFERENCES products(product_id)
);

CREATE TABLE photos (
  photo_id INT GENERATED BY DEFAULT AS IDENTITY,
  style_id INT,
  photo_url TEXT,
  thumbnail_url TEXT,
  PRIMARY KEY(photo_id),
  CONSTRAINT fk_style_id
    FOREIGN KEY(style_id)
      REFERENCES styles(style_id)
);

CREATE TABLE skus (
  sku_id INT GENERATED BY DEFAULT AS IDENTITY,
  style_id INT,
  size TEXT,
  quantity INT,
  PRIMARY KEY(sku_id),
  CONSTRAINT fk_style_id
    FOREIGN KEY(style_id)
      REFERENCES styles(style_id)
);