Database Design Suggestion for Ordering System

0 votes

0 answers

33 views

This migration was made using Prisma ORM and using MYSQL as the main database. This schema is exclusively made for an ordering system I would just like to ask for suggestions on whether or not the relations/fields within these tables are necessary and if there are any performance issues with the way I set up the constraints, indexes, etc. I also didn't bother to remove the comments as it might also help with why I inputted certain things etc. I am also not fully sure about the way I've set up roles, and permission for setting up administrator roles for users. I would love to hear suggestions/changes/additions/etc. Prisma syntax isn't fully supported by code blocks on this website. You may use this for syntax highlighting. [Prisma Playground](https://playground.prisma.io/)

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}

//  This model will define the role of a user, i.e admin, customer, etc
model Role {
  id   Int    @id @default(autoincrement())
  name String @unique // A role should have a unique name. i.e admin, customer

  permissions Permission[] // A role can have many permissions
  users       User[] // A role can have many users
}

// 
model Permission {
  id   Int    @id @default(autoincrement())
  name String @unique // A permission should have a unique name. i.e create, read, update, delete, etc

  roles Role[] // A permission can have many roles
}

model User {
  id             Int       @id @default(autoincrement())
  username       String    @unique
  email          String    @unique
  emailVerified  DateTime?
  phoneNumber    String?
  hashedPassword String
  image          String?
  roleId         Int?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  role   Role?   @relation(fields: [roleId], references: [id]) // A user can have one role
  cart   Cart[]
  orders Order[] // A user can have many orders

  @@index([roleId], name: "role_id")
}

model Cart {
  id     Int @id @default(autoincrement())
  userId Int // A cart is associated with a user

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  cartItems CartItem[] // A cart can have many items
  users     User       @relation(fields: [userId], references: [id]) // A cart is only associated with one user

  @@unique([userId], name: "unique_user_id") // A user can only have one cart, reenforced by the unique constraint
  @@index([userId], name: "user_id")
}

model CartItem {
  id       Int @id @default(autoincrement())
  quantity Int

  cartId    Int
  productId Int

  product Product @relation(fields: [productId], references: [id]) // A cart item is associated with a product
  cart    Cart    @relation(fields: [cartId], references: [id]) // A cart item is associated with a cart

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([cartId], name: "cart_items_cart_id")
  @@index([productId], name: "cart_items_product_id")
}

model Category {
  id   Int    @id @default(autoincrement())
  name String @unique(map: "unique_category_name")

  products Product[] // A category can have many products

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([name], name: "unique_category_name") // A category should have a unique name
}

// Categories might be associated with different types, i.e drinks and foods product have similar categories, but overall have different properties associated with them, not important for now 

// This model will represent the overall order of a user and its information
model Order {
  id            Int     @id @default(autoincrement())
  userId        Int
  orderStatus   String
  paymentMethod String?
  transactionId String?
  // shippingAddress String?
  totalAmount   Float?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  transaction Transaction?
  isPaid      Boolean      @default(false)

  orderItems OrderItem[] // An order can have many items
  user       User        @relation(fields: [userId], references: [id]) // An order is associated with a user

  @@index([userId], name: "user_id")
}

// This model will represent each individual item in an order
model OrderItem {
  id        Int @id @default(autoincrement())
  orderId   Int
  productId Int
  quantity  Int

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  product Product @relation(fields: [productId], references: [id]) // An order item is associated with a product
  order   Order   @relation(fields: [orderId], references: [id], map: "order_items_order_id") // An order item is associated with an order

  @@index([orderId], name: "order_items_order_id")
  @@index([productId], name: "order_items_product_id")
}

model Product {
  id          Int      @id @default(autoincrement())
  name        String
  description String
  price       Float
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  images     Image[]
  inventory  Inventory[]
  cartItems  CartItem[] // A product can have many cart items
  orderItems OrderItem[] // A product can have many order items
  category   Category?   @relation(fields: [categoryId], references: [id]) // A product is associated with a category
  categoryId Int? // Foreign Key

  @@index([categoryId])
}

// This model will represent the images associated with a product
model Image {
  id        Int     @id @default(autoincrement())
  url       String  @unique(map: "unique_image_url")
  productId Int
  product   Product @relation(fields: [productId], references: [id], map: "image_product_id") // An image is associated with a product

  @@index([productId], name: "image_product_id")
}

model Inventory {
  id Int @id @default(autoincrement())

  productId Int // Foreign Key

  quantity  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  product Product @relation(fields: [productId], references: [id], map: "inventory_product_id") // An inventory is associated with a product

  @@index([productId], name: "inventory_product_id")
}

// This model will represent the transaction associated with an order
model Transaction {
  id                Int     @id @default(autoincrement())
  transactionId     String
  orderId           Int
  totalAmount       Float
  transactionStatus String
  paymentMethod     String?

  order Order @relation(fields: [orderId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([orderId], name: "unique_order_id")
  @@index([orderId], name: "order_id")
}

and per request here is the SQL queries that was automatically generated by the Prisma ORM

CREATE TABLE _PermissionToRole (
	A int NOT NULL,
	B int NOT NULL,
	UNIQUE KEY _PermissionToRole_AB_unique (A, B),
	KEY _PermissionToRole_B_index (B)
)

CREATE TABLE Cart (
	id int NOT NULL AUTO_INCREMENT,
	userId int NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY Cart_userId_key (userId),
	KEY user_id (userId)
)

CREATE TABLE CartItem (
	id int NOT NULL AUTO_INCREMENT,
	quantity int NOT NULL,
	cartId int NOT NULL,
	productId int NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	KEY cart_items_cart_id (cartId),
	KEY cart_items_product_id (productId)
)

CREATE TABLE Category (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY unique_category_name (name),
	UNIQUE KEY Category_name_key (name)
)

CREATE TABLE Image (
	id int NOT NULL AUTO_INCREMENT,
	url varchar(191) NOT NULL,
	productId int NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY unique_image_url (url),
	KEY image_product_id (productId)
)

CREATE TABLE Inventory (
	id int NOT NULL AUTO_INCREMENT,
	productId int NOT NULL,
	quantity int NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	KEY inventory_product_id (productId)
)

CREATE TABLE Order (
	id int NOT NULL AUTO_INCREMENT,
	userId int NOT NULL,
	orderStatus varchar(191) NOT NULL,
	paymentMethod varchar(191),
	transactionId varchar(191),
	totalAmount double,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	isPaid tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (id),
	KEY user_id (userId)
)

CREATE TABLE OrderItem (
	id int NOT NULL AUTO_INCREMENT,
	orderId int NOT NULL,
	productId int NOT NULL,
	quantity int NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	KEY order_items_order_id (orderId),
	KEY order_items_product_id (productId)
)

CREATE TABLE Permission (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY Permission_name_key (name)
)

CREATE TABLE Product (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL,
	description varchar(191) NOT NULL,
	price double NOT NULL,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	categoryId int,
	PRIMARY KEY (id),
	KEY Product_categoryId_idx (categoryId)
)

CREATE TABLE Role (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY Role_name_key (name)
)

CREATE TABLE Transaction (
	id int NOT NULL AUTO_INCREMENT,
	transactionId varchar(191) NOT NULL,
	orderId int NOT NULL,
	totalAmount double NOT NULL,
	transactionStatus varchar(191) NOT NULL,
	paymentMethod varchar(191),
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY Transaction_orderId_key (orderId),
	KEY order_id (orderId)
)

CREATE TABLE User (
	id int NOT NULL AUTO_INCREMENT,
	username varchar(191) NOT NULL,
	email varchar(191) NOT NULL,
	emailVerified datetime(3),
	phoneNumber varchar(191),
	hashedPassword varchar(191) NOT NULL,
	image varchar(191),
	roleId int,
	createdAt datetime(3) NOT NULL DEFAULT current_timestamp(3),
	updatedAt datetime(3) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY User_username_key (username),
	UNIQUE KEY User_email_key (email),
	KEY role_id (roleId)
)

I don't know if this might be useful ENGINE InnoDB, CHARSET utf8mb4, COLLATE utf8mb4_unicode_ci;

Asked by Daniel Evans (1 rep)

Oct 6, 2023, 10:43 AM
Last activity: Oct 6, 2023, 11:48 PM

Database Design Suggestion for Ordering System

Related Questions