Learning of SQL Day 44

 

Day 44: NoSQL Data Modeling

Introduction:

• Data modeling in NoSQL databases differs from traditional relational data modeling. Instead of using tables, rows, and columns, NoSQL data modeling focuses on the specific needs of the application and the types of queries that will be performed. It's important to design schemas that maximize performance, scalability, and flexibility.

Key Concepts:

• Schema Design: Tailored to the specific NoSQL database and use case.

• Denormalization: Frequently used in NoSQL to optimize read performance by avoiding joins.

• Data Partitioning: Distributing data across multiple servers to improve scalability.

• Data Consistency: Ensuring data integrity across different nodes and partitions.

Common NoSQL Data Models:

1. Document Store (MongoDB):

   • Flexible schema, ideal for hierarchical data and complex queries.

json

// Example of a nested document for an e-commerce application
{
    "orderID": 12345,
    "customer": {
        "customerID": 1,
        "name": "John Doe",
        "email": "john.doe@example.com"
    },
    "items": [
        {
            "productID": 101,
            "productName": "Laptop",
            "quantity": 1,
            "price": 999.99
        },
        {
            "productID": 102,
            "productName": "Mouse",
            "quantity": 2,
            "price": 19.99
        }
    ],
    "orderDate": "2023-04-01",
    "status": "Shipped"
}

2. Key-Value Store (Redis):

   • Simple schema, ideal for caching and session management.

redis

// Example of key-value pairs for user session data
SET session:12345:userID 1
SET session:12345:cartItems '[{"productID": 101, "quantity": 1}, {"productID": 102, "quantity": 2}]'
SET session:12345:status 'active'

3. Column Store (Apache Cassandra):

   • Scalable schema, ideal for large datasets and high write throughput.

sql

-- Example of a table for storing user activity logs
CREATE TABLE user_activity (
    userID INT,
    activityDate DATE,
    activityType TEXT,
    activityDetails TEXT,
    PRIMARY KEY (userID, activityDate)
);

-- Insert data into the user_activity table
INSERT INTO user_activity (userID, activityDate, activityType, activityDetails) VALUES (1, '2023-04-01', 'login', 'User logged in from IP 192.168.1.1');
INSERT INTO user_activity (userID, activityDate, activityType, activityDetails) VALUES (1, '2023-04-01', 'purchase', 'User purchased productID 101');

4. Graph Database (Neo4j):

   • Schema for relationships, ideal for social networks and recommendation engines.

cypher

// Example of nodes and relationships for a social network
CREATE (alice:User {userID: 1, name: "Alice"})
CREATE (bob:User {userID: 2, name: "Bob"})
CREATE (carol:User {userID: 3, name: "Carol"})
CREATE (alice)-[:FRIEND]->(bob)
CREATE (bob)-[:FRIEND]->(carol)
CREATE (alice)-[:FRIEND]->(carol)

// Query to find friends of friends of Alice
MATCH (alice:User {name: "Alice"})-[:FRIEND]->(fof:User)-[:FRIEND]->(fof2:User)
RETURN fof2.name;

Practice Exercise:

1. Document Store: Write a MongoDB document for a blog post with comments.

2. Key-Value Store: Write Redis commands to set and get user preferences.

3. Column Store: Write a Cassandra query to create a table for product reviews and insert a review.

4. Graph Database: Write a Neo4j query to create nodes and relationships for a product recommendation system.

json

// MongoDB document for a blog post with comments
{
    "postID": 1,
    "title": "Introduction to NoSQL",
    "content": "NoSQL databases provide a flexible way to store data...",
    "author": "Alice",
    "date": "2023-04-02",
    "comments": [
        {
            "commentID": 1,
            "user": "Bob",
            "comment": "Great post!",
            "date": "2023-04-03"
        },
        {
            "commentID": 2,
            "user": "Carol",
            "comment": "Very informative.",
            "date": "2023-04-04"
        }
    ]
}

redis

// Redis commands to set and get user preferences
SET user:1:theme "dark"
SET user:1:language "en"
GET user:1:theme
GET user:1:language

sql

-- Cassandra query to create a table for product reviews and insert a review
CREATE TABLE product_reviews (
    productID INT,
    reviewID INT,
    userID INT,
    rating INT,
    review TEXT,
    reviewDate DATE,
    PRIMARY KEY (productID, reviewID)
);

-- Insert a review into the product_reviews table
INSERT INTO product_reviews (productID, reviewID, userID, rating, review, reviewDate) VALUES (101, 1, 1, 5, 'Excellent product!', '2023-04-02');

cypher

// Neo4j query to create nodes and relationships for a product recommendation system
CREATE (user1:User {userID: 1, name: "Alice"})
CREATE (user2:User {userID: 2, name: "Bob"})
CREATE (product1:Product {productID: 101, name: "Laptop"})
CREATE (product2:Product {productID: 102, name: "Mouse"})
CREATE (user1)-[:PURCHASED]->(product1)
CREATE (user2)-[:PURCHASED]->(product1)
CREATE (user2)-[:PURCHASED]->(product2)

// Query to find products purchased by users who bought the same products as Alice
MATCH (alice:User {name: "Alice"})-[:PURCHASED]->(p:Product)<-[:PURCHASED]-(otherUser:User)-[:PURCHASED]->(otherProduct:Product)
WHERE alice <> otherUser
RETURN otherProduct.name;

Important Tips:

• Choose the right NoSQL data model based on the specific requirements of your application.

• Design schemas to optimize read and write performance by leveraging denormalization and partitioning.

• Regularly review and update data models to accommodate changing application needs and data access patterns.

Mastering NoSQL data modeling enhances your ability to design scalable and efficient databases for modern applications.