DB Dilemmas : User Service for E-Commerce, Part 3
Let's first write down the Requirements from Product Team on what they want, I'd like to put strong emphasis on how good Tech is built in a Top Down fashion i.e understanding the Business first.
Allow users to register and manage their profiles.
Provide secure authentication mechanisms.
Offer features like password reset, email confirmation, and two-factor authentication.
Enable admin or super-users to search, filter, and manage user profiles.
Ensure quick response times for user authentication and profile loading.
Handle peak traffic during promotional events or major announcements.
Data Privacy and Security:
Ensure user data, especially sensitive data like passwords, is securely stored and encrypted.
Comply with data protection regulations like GDPR.
- The service should handle the rapid growth of users, especially in the early phases after launch.
- Minimize downtime to ensure a seamless user experience.
- The ability to easily add new features or modify existing ones based on user feedback and changing business needs.
- Seamless integration with other services, such as billing or order management, if they exist.
- Regular updates without disrupting the user experience.
- Cost-effective solutions without compromising on quality and security.
Now let's translate these into tech requirements
|10%: CRUD operations on user data, 5%: authentication queries, 85%: user metadata search.
|For every 100 database calls: 10 for CRUD, 5 for authentication, 85 for metadata search.
|Performance and Scalability
|10-100ms for user authentication and profile retrieval.
|100% user growth year-over-year for the first three years.
|Availability and Fault Tolerance
|No more than 10 minutes of unplanned downtime per month.
|Daily backups with the ability to restore data within 2 hours.
|Data Model and Flexibility
|Moderate changes to the user schema within the first year.
|Some flexibility for user metadata, but core attributes like UserID, email, password hash remain consistent.
|Some user attributes may be optional and can have null values.
|Yes, for designing event-driven calls and integrations.
|Moderate – search by attributes, filter, sort operations for dashboards.
|Needed especially for user registration and profile updates.
|Relationships between users and other entities like user roles, permissions.
|Data Types and Structure
|Strings (names, emails), hashes (passwords), date/time (timestamps), integers (age, userIDs), JSON/BLOB (metadata).
|Moderate use for comprehensive profiles or admin dashboards.
|Normalized vs. De-Normalized
|Mostly normalized, some de-normalization for frequently accessed attributes.
|Essential for sensitive data like passwords.
|Required for accessing user data.
|Moderate – caching of active user sessions and profiles.
|Connection pooling for concurrent user logins and operations.
|Regular updates for security patches, occasional schema migrations.
Let's do Trade offs between Databases now
Finally... What should we go with?
Primary Choice: CockroachDB seems to be a strong fit considering the requirements. It provides the relational capabilities of traditional RDBMS, ensuring data integrity, while also offering the scalability of NoSQL databases. Its built-in fault tolerance and strong consistency model align well with the requirements.
CockroachDB also supports Online Schema changes in Contrary to Stop the world Schema changes in mysql or Postgresql so no Downtime even during that
But.. given that CockroachDB is a budding solution and. doesn't have
- extensive community support
- Vendor Reputation
- ease of use
- Managed Cloud solutions like : AWS-managed DB
We have requirements for Strong Consistency and ACID, we can also go with either PostgreSQL or MariaDB but Schema flexibility will be affected due to this