smart-system-db.sql

DROP TABLE IF EXISTS foods CASCADE;
CREATE TABLE foods (
  id varchar(40) not null unique default uuid_generate_v4(),
  name text,
  description text,
  img text,
  taste_type jsonb not null default '{}',
  created_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  updated_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  deleted_at TIMESTAMP WITHOUT TIME ZONE default null
);

DROP TABLE IF EXISTS food_savours CASCADE;
CREATE TABLE food_savours (
  food_id varchar(40) not null,
  savour_id text not null,
  created_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  updated_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  deleted_at TIMESTAMP WITHOUT TIME ZONE default null,

  CONSTRAINT fk_food_savours_savour_id FOREIGN KEY(savour_id) REFERENCES savours(id),
  CONSTRAINT fk_food_savours_food_id FOREIGN KEY(food_id) REFERENCES foods(id)
);


DROP TABLE IF EXISTS savours CASCADE;
CREATE TABLE savours (
  id text not null unique default uuid_generate_v4(),
  name text,
  taste_type jsonb not null default '{}',
  created_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  updated_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  deleted_at TIMESTAMP WITHOUT TIME ZONE default null
);


DROP TABLE IF EXISTS users CASCADE;
CREATE TABLE users (
  id varchar(40) not null unique default uuid_generate_v4(),
  email text,
  name text,
  created_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  updated_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  deleted_at TIMESTAMP WITHOUT TIME ZONE default null
);


DROP TABLE IF EXISTS user_rating CASCADE;
CREATE TABLE user_rating (
  user_id varchar(40) not null,
  food_id varchar(40) not null,
  stars integer default 0,
  created_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  updated_at TIMESTAMP WITHOUT TIME ZONE not null default now(),
  deleted_at TIMESTAMP WITHOUT TIME ZONE default null,

  CONSTRAINT fk_user_rating_user_id FOREIGN KEY(user_id) REFERENCES users(id),
  CONSTRAINT fk_user_rating_food_id FOREIGN KEY(food_id) REFERENCES foods(id)
);