Harnessing Data Architecture: Practical Models and SQL Solutions

Mar 26, 2024

In the dynamic landscape of data engineering, the ability to design and implement effective data models stands as a cornerstone of technological advancement and operational efficiency. Pranav Arora, with a rich background as a Data Engineer and extensive experience across diverse sectors, presents a compilation of real-world data modeling examples. These examples, ranging from eCommerce customer data models to complex healthcare patient records systems, not only highlight the practical applications of data architecture but also serve as a learning tool for aspiring data professionals. This blog delves into the intricacies of SQL schema creation, showcasing solutions tailored for various industries, and illuminates the path for those keen on exploring the depths of data engineering and architecture.

1. Customer Data Model for eCommerce

Scenario: An eCommerce platform needs a model to store customer information, orders, and the products they purchase.

Solution:

Tables: Customers, Orders, Products, OrderDetails.
Relationships: A customer can have multiple orders; an order can contain multiple products.

SQL Code:

-- Create tables
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Address VARCHAR(255)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10,2)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

2. Healthcare Patient Records System

Scenario: A healthcare provider requires a database to manage patient records, treatments, and hospital visits.

Solution:

Tables: Patients, Treatments, Visits, PatientTreatments.
Relationships: Patients have multiple visits and treatments.

SQL Code:

CREATE TABLE Patients (
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100),
    DOB DATE,
    Gender CHAR(1)
);

CREATE TABLE Treatments (
    TreatmentID INT PRIMARY KEY,
    TreatmentName VARCHAR(100),
    Description TEXT
);

CREATE TABLE Visits (
    VisitID INT PRIMARY KEY,
    PatientID INT,
    VisitDate DATE,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);

CREATE TABLE PatientTreatments (
    PatientTreatmentID INT PRIMARY KEY,
    PatientID INT,
    TreatmentID INT,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (TreatmentID) REFERENCES Treatments(TreatmentID)
);

3. Inventory Management for Retail

Scenario: A retail business needs to track inventory, including products, suppliers, and stock levels.

Solution:

Tables: Products, Suppliers, ProductSuppliers, Inventory.
Relationships: Products can come from multiple suppliers; inventory tracks the stock of products.

SQL Code:

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY,
    Name VARCHAR(100),
    ContactInfo VARCHAR(255)
);

CREATE TABLE ProductSuppliers (
    ProductID INT,
    SupplierID INT,
    PRIMARY KEY (ProductID, SupplierID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);

CREATE TABLE Inventory (
    InventoryID INT PRIMARY KEY,
    ProductID INT,
    QuantityInStock INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

4. University Database Management

Scenario: A university wants to maintain records of students, courses, and enrollments.

Solution:

Tables: Students, Courses, Enrollments.
Relationships: Students can enroll in multiple courses.

SQL Code:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Major VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credits INT
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    Grade CHAR(2),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

5. Real Estate Properties Listing

Scenario: A real estate agency needs a database to list properties, agents, and property views by potential buyers.

Solution:

Tables: Properties, Agents, Views.
Relationships: Properties are assigned to agents; properties can be viewed multiple times.

SQL Code:

CREATE TABLE Agents (
    AgentID INT PRIMARY KEY,
    Name VARCHAR(100),
    ContactInfo VARCHAR(255)
);

CREATE TABLE Properties (
    PropertyID INT PRIMARY KEY,
    Address VARCHAR(255),
    Price DECIMAL(10,2),
    AgentID INT,
    FOREIGN KEY (AgentID) REFERENCES Agents(AgentID)
);

CREATE TABLE Views (
    ViewID INT PRIMARY KEY,
    PropertyID INT,
    ViewDate DATE,
    ViewerName VARCHAR(100),
    FOREIGN KEY (PropertyID) REFERENCES Properties(PropertyID)
);

6. Social Media Analytics

Scenario: A company monitors social media posts, user engagement, and trending topics.

Solution:

Tables: Posts, Users, Topics, PostTopics, Engagements.
Relationships: Posts can be associated with multiple topics and can have various types of engagements (likes, shares).

SQL Code:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(100),
    RegistrationDate DATE
);

CREATE TABLE Posts (
    PostID INT PRIMARY KEY,
    UserID INT,
    Content TEXT,
    PostDate DATE,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

CREATE TABLE Topics (
    TopicID INT PRIMARY KEY,
    TopicName VARCHAR(100)
);

CREATE TABLE PostTopics (
    PostID INT,
    TopicID INT,
    PRIMARY KEY (PostID, TopicID),
    FOREIGN KEY (PostID) REFERENCES Posts(PostID),
    FOREIGN KEY (TopicID) REFERENCES Topics(TopicID)
);

CREATE TABLE Engagements (
    EngagementID INT PRIMARY KEY,
    PostID INT,
    EngagementType VARCHAR(50),
    EngagementDate DATE,
    FOREIGN KEY (PostID) REFERENCES Posts(PostID)
);

7. Hotel Booking System

Scenario: A hotel chain manages rooms, bookings, and customers across multiple locations.

Solution:

Tables: Customers, Hotels, Rooms, Bookings.
Relationships: Customers make bookings for rooms in hotels.

SQL Code:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE TABLE Hotels (
    HotelID INT PRIMARY KEY,
    Name VARCHAR(100),
    Location VARCHAR(255)
);

CREATE TABLE Rooms (
    RoomID INT PRIMARY KEY,
    HotelID INT,
    Type VARCHAR(50),
    Price DECIMAL(10,2),
    FOREIGN KEY (HotelID) REFERENCES Hotels(HotelID)
);

CREATE TABLE Bookings (
    BookingID INT PRIMARY KEY,
    CustomerID INT,
    RoomID INT,
    CheckInDate DATE,
    CheckOutDate DATE

By dissecting scenarios from eCommerce to social media analytics, we grasp the versatility and necessity of adept data modeling in solving real-world problems.

The AI Memory Cell

Discussion about this post

Ready for more?