DB Dilemmas : User Service for E-Commerce, Part 3

DB Dilemmas : User Service for E-Commerce, Part 3
Play this article

Product Requirements

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.

  1. Functionality:

    • 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.

  2. Performance:

    • Ensure quick response times for user authentication and profile loading.

    • Handle peak traffic during promotional events or major announcements.

  3. Data Privacy and Security:

    • Ensure user data, especially sensitive data like passwords, is securely stored and encrypted.

    • Comply with data protection regulations like GDPR.

  4. Scalability:

    • The service should handle the rapid growth of users, especially in the early phases after launch.
  5. Availability:

    • Minimize downtime to ensure a seamless user experience.
  6. Flexibility:

    • The ability to easily add new features or modify existing ones based on user feedback and changing business needs.
  7. Integration:

    • Seamless integration with other services, such as billing or order management, if they exist.
  8. Maintenance:

    • Regular updates without disrupting the user experience.
  9. Budget:

    • Cost-effective solutions without compromising on quality and security.

Now let's translate these into tech requirements

Query PatternsTypes10%: CRUD operations on user data, 5%: authentication queries, 85%: user metadata search.
FrequencyFor every 100 database calls: 10 for CRUD, 5 for authentication, 85 for metadata search.
Performance and ScalabilityRead/Write Ratio100:1
Expected Latency10-100ms for user authentication and profile retrieval.
Growth100% user growth year-over-year for the first three years.
Availability and Fault ToleranceDowntimeNo more than 10 minutes of unplanned downtime per month.
BackupDaily backups with the ability to restore data within 2 hours.
Data Model and FlexibilityAlterationsModerate changes to the user schema within the first year.
Schema FlexibilitySome flexibility for user metadata, but core attributes like UserID, email, password hash remain consistent.
Null ValuesSome user attributes may be optional and can have null values.
Change EventsYes, for designing event-driven calls and integrations.
ComplexityQuery ComplexityModerate – search by attributes, filter, sort operations for dashboards.
ACID ComplianceNeeded especially for user registration and profile updates.
Referential IntegrityRelationships between users and other entities like user roles, permissions.
Data Types and StructureData TypesStrings (names, emails), hashes (passwords), date/time (timestamps), integers (age, userIDs), JSON/BLOB (metadata).
JoinsModerate use for comprehensive profiles or admin dashboards.
Normalized vs. De-NormalizedMostly normalized, some de-normalization for frequently accessed attributes.
SecurityData EncryptionEssential for sensitive data like passwords.
AuthenticationRequired for accessing user data.
OperationalMemory UsageModerate – caching of active user sessions and profiles.
PoolingConnection pooling for concurrent user logins and operations.
MaintenanceRegular updates for security patches, occasional schema migrations.

Let's do Trade offs between Databases now

Finally... What should we go with?

CockroachDB - Wikipedia

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