API and DB design with Boolean

Challenges with boolean type in API and database design.
Some guidelines when to use what.

Transcript

1. API and DB design with Boolean
2. Boolean • true, false • null (tri-state)
3. Boolean • API (REST/GraphQL) • Language APIs • Database
4. Challenges • Extensibility • Domain Clarity • Readability and Maintainability
5. Extensibility Example: GitHub API — Create new repository // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first awesome repository", "private": false, ... }
6. Extensibility Example: GitHub API — Create new organization repo // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first awesome repository", "private": false, ... } //HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first repository", "private": false, "visibility": "internal" | "public" | "private" ... } // internal — restricted to members of an organization // public — publicly accessible from the internet // private — restricted to repository creators and collaborators
7. Extensibility Example: GitHub API — Create new repository - with capability // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first repository", "private": false, "has_issues": true, "has_projects": true, "has_wiki": false, "has_downloads": true }
8. Extensibility Example: GitHub API — Create new repository - with capability // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first repository", "private": false, "has_issues": true, "has_projects": true, "has_wiki": false, "has_downloads": true } // Client if (has_project && has_downloads && has_issues && has_wikies) { } if (has_project && has_downloads && has_issues && has_wikies == fase) { } 4 Booleans produced 16 different permutations
9. Extensibility Example: GitHub API — Create new repository - with capability // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first repository", "private": false, "has_issues": true, "has_projects": true, "has_wiki": false, "has_downloads": true } // HTTP Request: POST /user/repos { "name": "my-awesome-project", "description": "This is your first repository", "private": false, "repo_capabilities": ["has_issues", "has_projects", "has_downloads"] }
10. Domain Clarity Example: Payments API — Send new payment // HTTP Request: POST /payments { "transactionId": "uniqueId", "paymentDetails": {...} "chargeAmount": 15.00, ... } // HTTP Response: { "isApproved": true, "approvedAmount": 15.00, ... }
11. Domain Clarity Example: Payments API — Send new payment - Gift card // HTTP Request: POST /payments { "transactionId": "uniqueId", "paymentDetails": {...} "chargeAmount": 15.00, ... } // HTTP Response: { "isApproved": true, "approvedAmount": 10.00, ... } "Partial Approvals" is implicit
12. Domain Clarity Example: Payments API — Send new payment - Gift card // HTTP Request: POST /payments { "transactionId": "uniqueId", "paymentDetails": {...} "chargeAmount": 15.00, ... } // HTTP Response: { "paymentStatus": "PARTIALLY_APPROVED" "approvedAmount": 10.00, "remainingAmount": 5.00, ... } paymentStatus with the enum value PARTIALLY_APPROVED // Client val(paymentStatus, remainingAmount) = paymentResponse if (paymentStatus.PARTIALLY_APPROVED) { var owedAmount = remainingAmount //re-display order total screen with owedAmount }
13. Code Readability and Maintainability Example: Payments API — Capability Configuration // HTTP Request: GET /store/{id}/configuration { "allow_apple_pay": true, "allow_google_pay": true, "allow_samsung_pay": false, ... } // Client val(paymentMethod) = paymentRequest if (paymentMethod == PaymentMethod.GOOGLE_PAY && storeConfig.allow_google_pay) { //process payment } else if (paymentMethod == PaymentMethod.APPLE_PAY && storeConfig.allow_apple_pay) { //process payment } else if (paymentMethod == PaymentMethod.SAMSUNG_PAY && storeConfig.allow_samsung_pay) { //process payment } else { //decline payment as unsupported } Do want to do this?
14. Code Readability and Maintainability Example: Payments API — Capability Configuration // HTTP Request: GET /store/{id}/configuration { "allowedPaymentMethods": ["GOOGLE_PAY", "APPLE_PAY"], ... } // Client val(paymentMethod) = paymentRequest if (paymentMethod in storeConfig.allowedPaymentMethods) { //process payment } else { //decline payment as unsupported }
15. When to use Booleans? Example: Example: Wallet API — Add a default payment option. // HTTP Request: POST /wallet/payment-options { "maskedNumber": "*******4433" "cardNetwork": "VISA", "nickname": "My Preferred Card", "default": true, ... }
16. FlagArgument // Pseudo-code class Concert { public Booking book(Customer aCustomer, boolean isPremium) {...} } // Preferred class Concert { public Booking regularBook(Customer aCustomer) {...} public Booking premiumBook(Customer aCustomer) {...} }
17. FlagArgument Tangled Implementation // Simple case public Booking book(Customer aCustomer, boolean isPremium) { if (isPremium) // logic for premium book else // logic for regular booking } // Tangled case public Booking book(Customer aCustomer, boolean isPremium) { lorem().ipsum(); dolor(); if (isPremium) sitAmet(); consectetur(); if (isPremium) adipiscing().elit(); else { aenean(); vitaeTortor().mauris(); } eu.adipiscing(); }
18. Effective Java Prefer two-element enum types to boolean parameters public enum TemperatureScale { FAHRENHEIT, CELSIUS } // Preferred Thermometer.newInstance(TemperatureScale.CELSIUS) // Over Thermometer.newInstance(true) //Boolean // Evolve in future without a new static factory to Thermometer public enum TemperatureScale { FAHRENHEIT, CELSIUS, KELVIN }
19. Bool vs. Enum vs. String • Use bool type if we want to have a fixed design and intentionally don't want to extend the functionality. For example: bool enable_tracing or bool enable_pretty_print. • Use an enum type if we want to have a flexible design but don't expect the design will change often. The rule of thumb is the enum definition will only change once a year or less often. For example: enum TlsVersion or enum HttpVersion. • Use string type if we have an open-ended design or the design can be changed frequently by an external standard. The supported values must be clearly documented. For example: • string region_code as defined by Unicode regions. • string language_code as defined by Unicode locales.
20. Database Is There a Boolean in SQL? Database Boolean? Use Instead Oracle No NUMBER(1) or CHAR(1) SQL Server No BIT MySQL No BIT or TYNYINT (BOOL*, BOOLEAN*) PostgreSQL Yes
21. Bool vs. Lookup tables vs. Chars • Use bool type if we want to have a fixed design and intentionally don't want to extend the functionality. And if you can avoid tri-state. For example: bool enable_tracing or bool enable_pretty_print. • Use Lookup tables if we want to have a flexible design and save table space at the cost of table joins. For example: customer_type or temperature_scale_type. • Use string/char/number type if we want to have a flexible design and table join performance is high at the cost of table space. The supported values must be clearly documented and have database check constraints. For example: • customer_type char(1) as R - Real, T - Test, D - Demo • region_code char(3) as usa, can, eu
22. References API • https://medium.com/geekculture/why-you-shouldnt-use-booleans-in-rest-apis-2747b187876c • https://docs.github.com/en/rest/reference/repos#create-an-organization-repository • https://martinfowler.com/bliki/FlagArgument.html • https://cloud.google.com/apis/design/design_patterns#bool_vs_enum_vs_string • https://cloud.google.com/blog/products/api-management/api-design-101-links-our-most-popular-posts Database • https://thoughtbot.com/blog/avoid-the-threestate-boolean-problem • https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html • https://www.databasestar.com/sql-boolean-data-type/