/ecommerce-database

this is database design and queries for ecommerce .

Primary LanguagePLpgSQL

ecommerce-database

this is database design and queries for ecommerce .

prerequisites:

  • postgresql.
  • docker or any other sql client.

Topics:

  • DDL
  • DML
  • Erd diagram
  • Stored Procedures.
  • Triggers.
  • Denormalization
  • Indexing
  • Concurrency control
  • Locking
  • Isolation levels
  • Query optimization techniques
  • views
  • explain analyze
  • tasks / challenges

database erd:

image

Project Structure:

├── Query Optimization Techniques.
│   ├── query_optimization.sql
│   ├── QUERY_OPTIMIZATION_TECHNIQUES.md
│
├── Denormalization
│   ├── denormalization.sql
│   ├── DENORMALIZATION.md
│
├── Indexing
│   ├── create_indexes.sql
│   ├── show_indexes_info.sql
│   ├── INDEXING.md
│
├── Concurrency Control
│   ├── concurrency_control.sql
│   ├── CONCURENCY_CONTROL.md
│
├── Locking
│   ├── locking.sql
│   ├── LOCKING.md
│
├── Isolation Levels
│   ├── isolation_levels.sql
│   ├── ISOLATION_LEVELS.md
│
├── DDL
│   ├── create_table.sql
│   ├── create_procedure.sql
│   ├── DDL.md
│
├── DML
│   ├── dml.sql
│   ├── DML.md
│
├── explain_analyze
│   ├── explain_analyze.sql
│   ├── EXPLAIN_ANALYZE.md
│
├── ERD
│   ├── ecommerce.png
│
├── tasks
│   ├── task1.sql
│   ├── task2.sql
│   ├── task3.sql
│   ├── task4.sql
│   ├── TASKS.md
├── README.md

Database Design:

  • Database Name: ecommerce
  • Tables:
    • users
    • products
    • orders
    • order_items
    • categories
    • reviews
    • product_reviews
    • cart
    • cart_items
    • addresses

Read more Details in each topic: