Step-by-step tutorial on how to set up the e-commerce database

03 Sep, 2024

Below is a step-by-step tutorial on how to set up the e-commerce database, where multiple item images are stored in a single field using JSON format. We'll be using MySQL, but the steps are generally applicable to other SQL databases with slight variations.

Step 1: Setting Up Your MySQL Environment

1. Install MySQL

• Windows: Download and install from MySQL's official website.
• macOS: Install using Homebrew: brew install mysql.
• Linux: Install using your package manager. For Ubuntu: sudo apt-get install mysql-server.

Follow the prompts to set up your MySQL root password and security options.

2. Start the MySQL Server

• Windows: Start MySQL from the Services app or via the command line.
• macOS/Linux: Start MySQL with the command: sudo service mysql start or mysql.server start.

3. Access the MySQL Command-Line Interface (CLI)

• Run mysql -u root -p and enter the root password you set during installation.

Step 2: Creating the E-Commerce Database

1. Create the Database

   CREATE DATABASE ecommerce_db;
   USE ecommerce_db;
   

2. Create the users Table

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       first_name VARCHAR(255),
       last_name VARCHAR(255),
       email VARCHAR(255) UNIQUE NOT NULL,
       password_hash VARCHAR(255) NOT NULL,
       address VARCHAR(255),
       phone_number VARCHAR(20),
       cart_id INT,  -- You can create a 'cart' table later if needed
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   

3. Create the categories Table

   CREATE TABLE categories (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       is_active BOOLEAN DEFAULT TRUE
   );
   

4. Create the items Table with JSON for Images

   CREATE TABLE items (
       id INT AUTO_INCREMENT PRIMARY KEY,
       category_id INT,  -- Foreign key reference to categories table
       name VARCHAR(255) NOT NULL,
       description TEXT,
       price DECIMAL(10, 2) NOT NULL,
       image_urls JSON,  -- JSON array to store multiple image URLs
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       FOREIGN KEY (category_id) REFERENCES categories(id)
   );
   

Step 3: Inserting Data into the Tables

1. Insert a Category

   INSERT INTO categories (name, is_active)
   VALUES ('Electronics', TRUE);
   

2. Insert an Item with Multiple Images (Using JSON)

   INSERT INTO items (category_id, name, description, price, image_urls)
   VALUES (1, 'Smartphone', 'Latest model smartphone with all the features.', 599.99, 
   '["https://example.com/images/smartphone1.jpg", "https://example.com/images/smartphone2.jpg"]');
   

3. Insert a User

   INSERT INTO users (first_name, last_name, email, password_hash, address, phone_number)
   VALUES ('John', 'Doe', 'john.doe@example.com', 'hashed_password', '123 Main St', '555-555-5555');
   

Step 4: Querying Data

1. Retrieve All Items with Images

   SELECT id, name, description, price, JSON_UNQUOTE(JSON_EXTRACT(image_urls, '$[0]')) AS first_image
   FROM items;
   

2. Retrieve All Active Categories

   SELECT * FROM categories WHERE is_active = TRUE;
   

3. Retrieve All Users

   SELECT id, first_name, last_name, email, address, phone_number FROM users;
   

Step 5: Connecting Your Database to an Application

To connect this database to your e-commerce application, you’ll need a backend server. Here’s a brief overview of how you can do this using Node.js and MySQL as an example:

1. Install MySQL Client for Node.js

   npm install mysql2
   

2. Set Up a Connection in Node.js

   const mysql = require('mysql2');
   
   const connection = mysql.createConnection({
     host: 'localhost',
     user: 'root',
     password: 'your_password',
     database: 'ecommerce_db'
   });
   
   connection.connect(err => {
     if (err) {
       console.error('Error connecting to the database:', err.stack);
       return;
     }
     console.log('Connected to the database.');
   });
   

3. Querying the Database in Node.js

   connection.query('SELECT * FROM items', (error, results) => {
     if (error) throw error;
     console.log(results);
   });
   

Step 6: Additional Tips

1. Backup and Security: Make sure to back up your database regularly and secure your MySQL server by setting up user roles and limiting access permissions.
2. Optimizing Queries: As your database grows, consider indexing frequently queried columns to optimize performance.
3. JSON Handling: If using JSON data types, ensure that your application can properly parse and manipulate JSON data.

Summary

• Database Schema: You created tables for users, categories, and items, storing item images as JSON in the items table.
• Data Insertion: You inserted sample data into the tables.
• Querying: You performed basic queries to retrieve data.
• Connecting to a Backend: We provided a simple example of connecting your database to a Node.js application.

This setup provides a flexible and scalable foundation for your e-commerce platform. You can expand it with more features like orders, payments, and shipping as your project grows.