atelierm-db-create.sql

CREATE TABLE "cards" (
  "id" serial,
  "card_type" varchar(20),
  "card_fee" money,
  PRIMARY KEY ("id")
);

CREATE TABLE "personalizations" (
  "id" serial,
  "font" varchar(15),
  "font_size" varchar(10),
  "max_length" integer,
  "personalization_color_id" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "FK" ON  "personalizations" ("personalization_color_id");

CREATE TABLE "order_details" (
  "id" serial,
  "monogram" varchar(15),
  "order_id" integer,
  "personalization_id" integer,
  "product_id" integer,
  "product_color_id" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "FK" ON  "order_details" ("order_id", "personalization_id", "product_id", "product_color_id");

CREATE TABLE "products" (
  "id" serial,
  "product_name" varchar(50),
  "product_color" varchar(15),
  "product_cost" money,
  "product_price" money,
  PRIMARY KEY ("id")
);

CREATE TABLE "packages" (
  "id" serial,
  "package_type" varchar(15),
  "package_fee" money,
  PRIMARY KEY ("id")
);

CREATE TABLE "users" (
  "id" serial,
  "email" varchar(50),
  "password" varchar(24),
  "first_name" varchar(20),
  "last_name" varchar(20),
  "mobile_phone" varchar(12),
  "role_id" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "FK" ON  "users" ("role_id");

CREATE TABLE "addresses" (
  "id" serial,
  "address_type" varchar(15),
  "address_line_1" varchar(25),
  "address_line_2" varchar(25),
  "address_line_3" varchar(35),
  "latitude" numeric(8, 5),
  "longitude" numeric(8, 5),
  "user_id" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "FK" ON  "addresses" ("user_id");

CREATE TABLE "delivery_options" (
  "id" serial,
  "delivery_type" varchar(20),
  "delivery_fee" money,
  PRIMARY KEY ("id")
);

CREATE TABLE "roles" (
  "id" serial,
  "role_name" varchar(20),
  PRIMARY KEY ("id")
);

CREATE TABLE "permissions" (
  "id" serial,
  "permission_name" varchar(40),
  PRIMARY KEY ("id")
);

CREATE TABLE "roles_permissions" (
  "role_id" integer,
  "permission_id" integer
);

CREATE INDEX "PK, FK" ON  "roles_permissions" ("role_id", "permission_id");

CREATE TABLE "orders" (
  "id" serial,
  "order_date" date,
  "delivery_date" date,
  "discount_coupon" varchar(20),
  "order_total" money,
  "comment" varchar(288),
  "card_message" varchar(288),
  "order_status" varchar(20),
  "user_id" integer,
  "package_id" integer,
  "payment_id" integer,
  "card_id" integer,
  "delivery_option_id" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "FK" ON  "orders" ("user_id", "package_id", "payment_id", "card_id", "delivery_option_id");

CREATE TABLE "payments" (
  "id" serial,
  "payment_date" date,
  "payment_amount" money,
  "payment_method_id" integer,
  PRIMARY KEY ("id", "payment_method_id")
);

CREATE TABLE "products_personalizations" (
  "product_id" integer,
  "personalization_id" integer
);

CREATE INDEX "PK, FK" ON  "products_personalizations" ("product_id", "personalization_id");

CREATE TABLE "personalization_colors" (
  "id" serial,
  "color_name" varchar(15),
  PRIMARY KEY ("id")
);

CREATE TABLE "products_product_colors" (
  "product_id" integer,
  "product_color_id" integer,
  "quantity" integer
);

CREATE INDEX "PK, FK" ON  "products_product_colors" ("product_id", "product_color_id");

CREATE TABLE "product_colors" (
  "id" serial,
  "color_name" varchar(15),
  PRIMARY KEY ("id")
);

CREATE TABLE "payment_methods" (
  "id" serial,
  "method_name" varchar(20),
  PRIMARY KEY ("id")
);