Skip to content

Instantly share code, notes, and snippets.

@wanderindev
Last active September 19, 2023 23:49
Show Gist options
  • Save wanderindev/e29f65fafbb3c686065ff5d5242d34a8 to your computer and use it in GitHub Desktop.
Save wanderindev/e29f65fafbb3c686065ff5d5242d34a8 to your computer and use it in GitHub Desktop.
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")
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment