Noah Saunders

Noah Saunders

1587960120

Database Design of Online Shopping Cart

This tutorial provides complete steps to design a database schema of online shops and shopping carts to manage the users, products, reviews, carts, orders, and payments. It can be further used to develop an online shop and shopping cart based websites or applications.

The Entity Relationship Diagram or visual database design is shown below.

Online Shopping Cart Database Design

Online Shopping Cart

Notes: It allows guest orders to place the order without logging in. The security can be handled by following RBAC Database in MySQL.

Shop Database

The very first step is to create the Shop Database. It can be created using the query as shown below.

CREATE SCHEMA `shop` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have used the character set utf8mb4 to support a wide range of characters.

User Table

In this section, we will design the User Table to store user information. The same table can be used to relate the product creators(from the admin panel) and orders placed on the website. Users can track their own orders and track the status. Below mentioned is the description of all the columns of the User Table.

The User Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `passwordHash` VARCHAR(32) NOT NULL,
  `admin` TINYINT(1) NOT NULL DEFAULT 0,
  `vendor` TINYINT(1) NOT NULL DEFAULT 0,
  `registeredAt` DATETIME NOT NULL,
  `lastLogin` DATETIME NULL DEFAULT NULL,
  `intro` TINYTEXT NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC) );

Product Table

In this section, we will design the Product Table to store the product data. Below mentioned is the description of all the columns of the Product Table.

It uses the column quantity to track the stock available in the product inventory to keep the design simple. It might be required to specify the quantity by several columns to cover a wide range of products. The possible columns could be sellQuantity, sellUnit, stockQuantity, and stockUnit where the sellQuantity and sellUnit can be used to be displayed on the Shop for the buyers and the stockQuantity and stockUnit can be used to track the inventory. It might also be required to convert the sellUnit to stockUnit while updating the inventory on placing the order. The Product Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`product` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NOT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL,
  `slug` VARCHAR(100) NOT NULL,
  `summary` TINYTEXT NULL,
  `type` SMALLINT(6) NOT NULL DEFAULT 0,
  `sku` VARCHAR(100) NOT NULL,
  `price` FLOAT NOT NULL DEFAULT 0,
  `discount` FLOAT NOT NULL DEFAULT 0,
  `quantity` SMALLINT(6) NOT NULL DEFAULT 0,
  `shop` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `startsAt` DATETIME NULL DEFAULT NULL,
  `endsAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC),
  INDEX `idx_product_user` (`userId` ASC),
  CONSTRAINT `fk_product_user`
    FOREIGN KEY (`userId`)
    REFERENCES `shop`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Product Meta

The Product Meta Table can be used to store additional information about products including the product banner URL etc. Below mentioned is the description of all the columns of the Product Meta Table.

The Product Meta Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`product_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `productId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_product` (`productId` ASC),
  UNIQUE INDEX `uq_product_meta` (`productId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Product Review Table

In this section, we will design the Product Review Table to store the product reviews. Below mentioned is the description of all the columns of the Product Review Table.

The Product Review Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`product_review` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `productId` BIGINT NOT NULL,
  `parentId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(100) NOT NULL,
  `rating` SMALLINT(6) NOT NULL DEFAULT 0,
  `published` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `publishedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_review_product` (`productId` ASC),
  CONSTRAINT `fk_review_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `shop`.`product_review` 
ADD INDEX `idx_review_parent` (`parentId` ASC);
ALTER TABLE `shop`.`product_review` 
ADD CONSTRAINT `fk_review_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `shop`.`product_review` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Category Table and Product Category Table

In this section, we will design the Category Table and Product Category Table to store the product categories and their mappings. Below mentioned is the description of all the columns of the Category Table.

The Category Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`category` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `parentId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL DEFAULT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`));

ALTER TABLE `shop`.`category` 
ADD INDEX `idx_category_parent` (`parentId` ASC);
ALTER TABLE `shop`.`category` 
ADD CONSTRAINT `fk_category_parent`
  FOREIGN KEY (`parentId`)
  REFERENCES `shop`.`category` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Below mentioned is the description of all the columns of the Product Category Table.

The Product Category Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`product_category` (
  `productId` BIGINT NOT NULL,
  `categoryId` BIGINT NOT NULL,
  PRIMARY KEY (`productId`, `categoryId`),
  INDEX `idx_pc_category` (`categoryId` ASC),
  INDEX `idx_pc_product` (`productId` ASC),
  CONSTRAINT `fk_pc_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pc_category`
    FOREIGN KEY (`categoryId`)
    REFERENCES `shop`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Tag Table and Product Tag Table

Similar to the category and product category tables, we can design the Tag Table and Product Tag Table. The major differences between the Category and Tag are listed below.

  • The parentId column is not required in the Tag Table.
  • The count of categories remains low since these can be used to form the Main Menu for navigational purposes. The tags can be more as compared to categories.
  • Both categories and tags can be used to relate the products.
  • One should assign only a few categories to a product whereas tags count can be more.

Cart Table and Cart Item Table

This section provides the tables to manage the virtual carts to store the user selection before creating the actual order. If the user cancels the payment or the payment fails, the same carts can be used as an abandoned cart by the marketing team to enquire about the buyers. A logged-in user can also be associated with the cart. Below mentioned is the description of all the columns of the Cart Table.

Notes: The Cart Table and Cart Item Table can be made optional if the Local Data, Session, or in-memory database like Redis is used to store the cart data. The same can be referred to create the order on payment success.

The Cart Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`cart` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NULL DEFAULT NULL,
  `sessionId` VARCHAR(100) NOT NULL,
  `token` VARCHAR(100) NOT NULL,
  `status` SMALLINT(6) NOT NULL DEFAULT 0,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `line1` VARCHAR(50) NULL DEFAULT NULL,
  `line2` VARCHAR(50) NULL DEFAULT NULL,
  `city` VARCHAR(50) NULL DEFAULT NULL,
  `province` VARCHAR(50) NULL DEFAULT NULL,
  `country` VARCHAR(50) NULL DEFAULT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_cart_user` (`userId` ASC),
  CONSTRAINT `fk_cart_user`
    FOREIGN KEY (`userId`)
    REFERENCES `shop`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Below mentioned is the description of all the columns of the Cart Item Table.

The Cart Item Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`cart_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `productId` BIGINT NOT NULL,
  `cartId` BIGINT NOT NULL,
  `sku` VARCHAR(100) NOT NULL,
  `price` FLOAT NOT NULL DEFAULT 0,
  `discount` FLOAT NOT NULL DEFAULT 0,
  `quantity` SMALLINT(6) NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_cart_item_product` (`productId` ASC),
  CONSTRAINT `fk_cart_item_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `shop`.`cart_item` 
ADD INDEX `idx_cart_item_cart` (`cartId` ASC);
ALTER TABLE `shop`.`cart_item` 
ADD CONSTRAINT `fk_cart_item_cart`
  FOREIGN KEY (`cartId`)
  REFERENCES `shop`.`cart` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Order Table and Order Item Table

This section provides the tables to manage the store orders. A logged-in user can also be associated with the order. Below mentioned is the description of all the columns of the Order Table.

The Order Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NULL DEFAULT NULL,
  `sessionId` VARCHAR(100) NOT NULL,
  `token` VARCHAR(100) NOT NULL,
  `status` SMALLINT(6) NOT NULL DEFAULT 0,
  `subTotal` FLOAT NOT NULL DEFAULT 0,
  `itemDiscount` FLOAT NOT NULL DEFAULT 0,
  `tax` FLOAT NOT NULL DEFAULT 0,
  `shipping` FLOAT NOT NULL DEFAULT 0,
  `total` FLOAT NOT NULL DEFAULT 0,
  `promo` VARCHAR(50) NULL DEFAULT NULL,
  `discount` FLOAT NOT NULL DEFAULT 0,
  `grandTotal` FLOAT NOT NULL DEFAULT 0,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `line1` VARCHAR(50) NULL DEFAULT NULL,
  `line2` VARCHAR(50) NULL DEFAULT NULL,
  `city` VARCHAR(50) NULL DEFAULT NULL,
  `province` VARCHAR(50) NULL DEFAULT NULL,
  `country` VARCHAR(50) NULL DEFAULT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_order_user` (`userId` ASC),
  CONSTRAINT `fk_order_user`
    FOREIGN KEY (`userId`)
    REFERENCES `shop`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Below mentioned is the description of all the columns of the Order Item Table.

The Order Item Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`order_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `productId` BIGINT NOT NULL,
  `orderId` BIGINT NOT NULL,
  `sku` VARCHAR(100) NOT NULL,
  `price` FLOAT NOT NULL DEFAULT 0,
  `discount` FLOAT NOT NULL DEFAULT 0,
  `quantity` SMALLINT(6) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_order_item_product` (`productId` ASC),
  CONSTRAINT `fk_order_item_product`
    FOREIGN KEY (`productId`)
    REFERENCES `shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `shop`.`order_item` 
ADD INDEX `idx_order_item_order` (`orderId` ASC);
ALTER TABLE `shop`.`order_item` 
ADD CONSTRAINT `fk_order_item_order`
  FOREIGN KEY (`orderId`)
  REFERENCES `shop`.`order` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Transaction Table

We also need a transaction table to track the order payments made by the buyer and for bookkeeping. We can also use the same table to record the partial or full refund of the order. Below mentioned is the description of all the columns of the Transaction Table.

The Transaction Table with the appropriate constraints is as shown below.

CREATE TABLE `shop`.`transaction` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NOT NULL,
  `orderId` BIGINT NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `type` SMALLINT(6) NOT NULL DEFAULT 0,
  `mode` SMALLINT(6) NOT NULL DEFAULT 0,
  `status` SMALLINT(6) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_transaction_user` (`userId` ASC),
  CONSTRAINT `fk_transaction_user`
    FOREIGN KEY (`userId`)
    REFERENCES `shop`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `shop`.`transaction` 
ADD INDEX `idx_transaction_order` (`orderId` ASC);
ALTER TABLE `shop`.`transaction` 
ADD CONSTRAINT `fk_transaction_order`
  FOREIGN KEY (`orderId`)
  REFERENCES `shop`.`order` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Address Table

An address table can be used to avoid the redundant columns in the Cart and Order table depending on the actual implementation. It can be directly mapped to the Cart Table and Order Table using the appropriate foreign keys.

Summary

In this tutorial, we have discussed the database design of an Online Shopping Cart to store the users and manage product inventory. It also provided the database design to manage the cart, store the cart items, and manage the orders on an online shop.

You may submit your comments to join the discussion. The complete database schema is also available on GitHub.

#mysql #database #web-development

What is GEEK

Buddha Community

Database Design of Online Shopping Cart

Phạm Thành

1620326027

Thank you

wp codevo

wp codevo

1608042336

JavaScript Shopping Cart - Javascript Project for Beginners

https://youtu.be/5B5Hn9VvrVs

#shopping cart javascript #hopping cart with javascript #javascript shopping cart tutorial for beginners #javascript cart project #javascript tutorial #shopping cart

Siphiwe Nair

Siphiwe Nair

1625133780

SingleStore: The One Stop Shop For Everything Data

  • SingleStore works toward helping businesses embrace digital innovation by operationalising “all data through one platform for all the moments that matter”

The pandemic has brought a period of transformation across businesses globally, pushing data and analytics to the forefront of decision making. Starting from enabling advanced data-driven operations to creating intelligent workflows, enterprise leaders have been looking to transform every part of their organisation.

SingleStore is one of the leading companies in the world, offering a unified database to facilitate fast analytics for organisations looking to embrace diverse data and accelerate their innovations. It provides an SQL platform to help companies aggregate, manage, and use the vast trove of data distributed across silos in multiple clouds and on-premise environments.

**Your expertise needed! **Fill up our quick Survey

#featured #data analytics #data warehouse augmentation #database #database management #fast analytics #memsql #modern database #modernising data platforms #one stop shop for data #singlestore #singlestore data analytics #singlestore database #singlestore one stop shop for data #singlestore unified database #sql #sql database

Edison Stark

Edison Stark

1598164783

How to Make a Good Database Design?

How to make a good database design? Why we should create a good design database? Database design is an essential skill of a software engineer. In some interviews, the interviewer can ask you a few questions about it. As far as I know, we have some database principles. There are a lot of definitions about them and you can search on google for more details. Based on my experience, I’ll write it simply.

After reading this article, you will understand things:

  • What is a good database design? Why we should create a good design database? How to make a good database design?
  • Design process
  • Define and use some rules
  • Normalization Rules
  • Integrity Rules
  • Column Indexing
  • Some notes and advice when we design a database

Database Design Overview

Firstly, What is database design?

“Database Design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate.” Source: wikipedia.org

Database design is a part of the Design Process when we develop software. Before doing database design, we have to complete software architecture (N-tier layer, Microservice, …) at the high-level. Database design is a very important step at the low-level. Design Process often creates by Senior Software Engineer or Software Architect who has a lot of experience in the IT field.

Image for post

Development Process. Source: Internet.

With a medium or big system, we usually choose and combine some databases to achieve our purpose. We need to support transactions and relationships: MySQL or PostgreSQL or SQL Server. We need to save flexible data: MongoDB(unstructured data). Support caching (Redis: key-value, sorted set, list, …), support full-text searching(Elastic Search, …), and so on.

Depends on your project, you should choose and combine some databases appropriately and wisely. There’s not the best database, only have database appropriately. We should take advantage of databases and know the limit/issues of them. In this article, I’ll only write about DBMS(Database Management System): MySQL. The reason is it’s complex more than NoSQL database such as MongoDB, Redis, and so on.

In some projects, the Senior Software Engineer or Solution Architect could request to make a Class Diagram and ERD (Entity Relationship Diagram). What the difference between the Class Diagram and ERD?

  • The class diagrams are **used to represent the main object or building block**of the system. They are used to **_show the relationship of one class with another _**and also represent the attributes of the system.
  • However, and ERD is more of a database in the form of tables. They don’t show individual relationships but relationship sets as well as sets of entities. They show the type of information that needs to be stored in the database.

In my opinion, we should make ERD and don’t create Class Diagrams unless we have some special reasons. This depends on your project.

#erd #database-design #good-database-design #design-db-process #database

Ananya Gupta

1613386017

Key Points For Joining The Best Web Designing For Better Career Option

The scope of the web designer is increasing day by day, because of high demanding job forms an important part of our society. Web Designing is that the creation and planning of internet sites. it’s a process of developing different sites.

To be an honest web designer you ought to have an entire knowledge of CSS and HTML. These are the important prerequisite for designing an internet site. In today’s world of competition to be amongst at the highest one needs media. Websites are playing a key role in today’s life. Whether in online web desiging course mention shopping or engineering everything is formed online.

These are some of the main benefits which a standard person has made with the utilization of internet sites. many roles are available for web designers than before. Many big companies demand experienced and quality web designers.

Web designing is the creative and innovative part of web development during which the designer is especially concerned with how our website looks. CSS is the heart of the web designing part.

The scope of web designing is increasing day by day. In today’s web and internet world, people want a creative website once they want to access it. Top company web development of India hands over 8-10 lac per annum for an experienced web designer. So it’s a really good field for creating websites.

Web designers have the work of designing whole websites which forms the primary step of web development. After web designing remaining work will start. In today’s growing scenario there are many job opportunities for fresher and experienced candidates.

Web designing is a crucial course that features a lot of scope within the present and also in the future scenario. There are two ways to travel through this course. If you’re curious about taking over a full-time web designer course then we will make a career in media or as advertising agents.

If one is curious about Engineering or in Commerce course but getting to develop the skill in web designing, then you’ll prefer the part-time short course in web designing.

When it comes to selecting a training institute, you will find them in every corner. CETPA Infotech is a reputed training institution and provides training that is industry oriented, updated, innovative and summer training has become a pioneer during this online designing field.

#web designing training in noida #web designing training in delhi #web designing online training #web designing online course #web designing course #web designing training

DataIT Solutions

DataIT Solutions

1609840501

Landscapes Website Design | Nature Landscapes Website Designer

Most landscapers think of their website as an online brochure. In reality of consumers have admitted to judging a company’s credibility based on their web design, making your website a virtual sales rep capable of generating massive amounts of leads and sales. If your website isn’t actively increasing leads and new landscaping contracts, it may be time for a redesign.

DataIT Solutions specializes in landscape website designing that are not only beautiful but also rank well in search engine results and convert your visitors into customers. We’ve specialized in the landscaping industry for over 10 years, and we look at your business from an owner’s perspective.

Why use our Landscapes for your landscape design?

  • Superior experience
  • Friendly personal service
  • Choice of design layout
  • Budget sensitive designs
  • Impartial product choice and advice
  • Planting and lighting designs

Want to talk about your website?
If you are a gardener or have a gardening company please do not hesitate to contact us for a quote.
Need help with your website? Get in touch

#nature landscapes website design #landscapes website design #website design #website designing #website designer #designer