KimTay Pet Supplies Database Management System

1. Overview

KimTay Pet Supplies is a rapidly growing retailer that offers everything from pet food and toys to grooming accessories. To keep up with expanding inventory, multiple sales channels, and a loyal customer base, the company invested in a robust Database Management System (DBMS) that provides realtime visibility into stock levels, sales performance, supplier contracts, and customer preferences.

The DBMS is built on PostgreSQL, chosen for its reliability, strong ACID compliance, and rich extension ecosystem. All application logic is accessed through a RESTful API layer built with Node.js and Express, while a lightweight frontend dashboard powered by React visualises the data for managers and store staff.

2. System Architecture

The architecture follows a threetier pattern:

2.1. Presentation Layer

• React SPA for admin and store users.
• Responsive design ensures usability on tablets and desktops.
• OAuth 2.0 integration with Azure AD for single signon.

2.2. Application Layer

• Node.js/Express API exposing CRUD endpoints.
• Business rules (price calculations, stock alerts) live here.
• Input validation performed with Joi, preventing malformed data.

2.3. Data Layer

• PostgreSQL 15 as the relational engine.
• TimescaleDB extension for timeseries data (sales trends).
• pgPartman for automatic table partitioning.
• Logical replication to a readonly reporting replica.

The diagram below summarises the flow:

[Browser]  HTTPS  [Load Balancer]  [Node.js API]  [PostgreSQL Master]  [Read Replica]    

3. Key Entities and Relationships

The core data model revolves around five primary entities. Relationships are captured in foreign keys and junction tables.

3.1. Products

3.2. Inventory

Tracks quantity by warehouse and batch.

CREATE TABLE inventory (    inventory_id SERIAL PRIMARY KEY,    product_id INT REFERENCES products(product_id),    warehouse_id INT REFERENCES warehouses(warehouse_id),    batch_number VARCHAR(30),    quantity INT CHECK (quantity >= 0),    expiry_date DATE,    last_updated TIMESTAMP DEFAULT now());

3.3. Orders

Customer orders are stored in orders with a onetomany link to order_items.

CREATE TABLE orders (    order_id SERIAL PRIMARY KEY,    customer_id INT REFERENCES customers(customer_id),    order_date TIMESTAMP DEFAULT now(),    status VARCHAR(20) CHECK (status IN ('Pending','Processing','Shipped','Cancelled')),    total_amount NUMERIC(12,2));

3.4. Suppliers

Each supplier may provide many products; a manytomany relationship is managed through supplier_products.

3.5. Customers

Basic demographic data plus loyalty tier.

All tables inherit from a common audit schema that adds created_by, updated_by, and deleted_at columns, enabling soft deletes and change tracking.

4. Common Queries

Below are representative SQL snippets that power daily operations.

4.1. Realtime Stock Availability

SELECT p.sku, p.name, SUM(i.quantity) AS total_stockFROM products pJOIN inventory i ON i.product_id = p.product_idGROUP BY p.sku, p.nameHAVING SUM(i.quantity) < 10ORDER BY total_stock ASC;

This query flags items with low inventory for automatic reorder.

4.2. TopSelling Products (Last 30 Days)

SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * p.price) AS revenueFROM order_items oiJOIN orders o ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.order_date >= now() - INTERVAL '30 days'GROUP BY p.nameORDER BY revenue DESCLIMIT 10;

4.3. Supplier Performance

SELECT s.name,       COUNT(sp.product_id) AS products_supplied,       AVG(DATE_PART('day', i.received_at - i.ordered_at)) AS avg_delivery_daysFROM suppliers sJOIN supplier_products sp ON sp.supplier_id = s.supplier_idJOIN inventory i ON i.product_id = sp.product_idGROUP BY s.nameORDER BY avg_delivery_days;

4.4. Customer Lifetime Value (CLV)

SELECT c.customer_id,       c.first_name,       c.last_name,       SUM(o.total_amount) AS lifetime_spend,       COUNT(o.order_id) AS order_countFROM customers cJOIN orders o ON o.customer_id = c.customer_idWHERE o.status <> 'Cancelled'GROUP BY c.customer_id, c.first_name, c.last_nameHAVING SUM(o.total_amount) > 500ORDER BY lifetime_spend DESC;

All queries are executed against the read replica to keep the primary node free for transactional workloads.

5. Security and Backup Strategy

• Authentication: Users authenticate via Azure AD; JWT tokens are verified on each API call.
• Authorization: Rowlevel security policies restrict access e.g., a store manager can only view inventory for his assigned warehouse.
• Encryption: Dataatrest is encrypted using AWS KMSmanaged keys; TLS 1.3 secures data in transit.
• Backup: Continuous PITR (PointInTime Recovery) with daily base backups and hourly WAL archiving.
• Disaster Recovery: A standby replica in a different region can be promoted within minutes.

6. Future Enhancements

KimTay plans to extend the DBMS in several directions:

1. MachineLearning Integration: Use PostgreSQLs pgml extension to predict demand spikes and recommend optimal reorder quantities.
2. Graph Extensions: Incorporate pggraph to model complex suppliercustomer relationships and detect indirect dependencies.
3. EventDriven Architecture: Stream change data capture (CDC) events to Kafka, enabling realtime sync with a datawarehouse for BI tools.
4. Mobile Companion App: Direct API endpoints for inventory scanning and quick stock adjustments on the shop floor.

These initiatives will keep KimTay Pet Supplies competitive while preserving data integrity and performance.