Database design
Below is the ER diagram, for the entities.
For the Online Coupon Service, we can utilize different databases based on the data structure requirements and the characteristics highlighted by the CAP theorem. Here are the suggestions:
1. Relational Database (SQL) - PostgreSQL or MySQL
- Entities: Transaction, User
- Database Type: SQL
- Reasoning: Ideal for structured data like user profiles and transactions, where relationships and ACID compliance (Atomicity, Consistency, Isolation, Durability) are important.
- CAP Theorem Focus: Consistency Focused - It provides strong consistency guarantees, making it suitable for transactions.
2. NoSQL Database (Document Store) - MongoDB
- Entities: Reward, Coupon, Store
- Database Type: NoSQL (Document Store)
- Reasoning: Suitable for flexible schema and unstructured data such as coupon details and user preferences, allowing easy scaling.
- CAP Theorem Focus: Availability Focused - It emphasizes availability and partition tolerance, making it well-suited for situations where the service needs to be continuously accessible even during network partitions.
3. In-Memory Database - Redis
- Database Type: NoSQL (In-Memory Key-Value Store)
- Reasoning: Best for caching frequently accessed data, such as the latest coupons and real-time notifications, to improve performance.
- CAP Theorem Focus: Availability Focused - By storing data in memory, it can serve requests very quickly, prioritizing quick access and higher availability over strong consistency in certain scenarios.
When designing the Online Coupon Service, applying partitioning, sharding, and scaling strategies for databases is crucial for enhancing performance, availability, and manageability. Here's how we can approach each aspect for the various database types we've discussed:
1. Partitioning and Sharding
a. Relational Database (PostgreSQL/MySQL)
- Partitioning:
- Vertical Partitioning: Split larger tables into smaller, related tables using a subset of columns (e.g., separating user information from transaction details).
- Horizontal Partitioning: Divide large tables into smaller tables (partitions) based on a specific key, such as user regions (e.g., partitioning users by geographical location).
- Sharding:
- Use a sharding approach to horizontally scale the database across multiple instances based on user ID ranges or geographic locations. This distributes the load and improves read/write performance.
b. NoSQL Document Store (MongoDB)
- Partitioning:
- MongoDB uses sharding natively, allowing automatic horizontal partitioning of data based on a chosen shard key (e.g., user ID or category ID for coupons).
- Sharding:
- Utilize sharding to distribute coupon documents across multiple replica sets, optimizing read/write operations for a large volume of coupons from various retailers.
2. Scaling Strategies
a. Relational Database
- Read Scaling:
- Implement read replicas to offload read traffic from the primary database instance, improving read performance during high-traffic periods.
- Write Scaling:
- Use master-slave replication or database clustering to handle a higher write load. Be mindful of potential latency between the primary and replicas.
b. NoSQL Document Store (MongoDB)
- Horizontal Scaling:
- Leverage auto-sharding capabilities to scale out by adding more shards (instances) as data grows, efficiently handling increased write and read requests.