## Some options for stricter value checks - Check that a text field contain undesired space before and after - `address text check (trim(address) = address)` - Check that a text is either null or contain some text - `address text check (address is null or length(address) >= 1)` - Check that values follow a standard - ISO codes - either with a row check, e.g. with regexes - `country_code text check (country_code ~ '^[A-Z]{2})` - or creating a dedicated enum - or creating a dedicated table ([example for currencies, languages, and locales here](https://github.com/umpirsky/country-list)) - `country_code text references country_code(iso_code)` - in Netherlands, there are EAN codes for grid operators - `grid_operator_ean_code text check (grid_operator_ean_code ~ '^[0-9]{13})` - Check that numbers allow expected ranges - `cost numeric check (cost >= 0)` - Check for expected number ranges - `insurance_years_granted check (insurance_years_granted >= 0 and insurance_years_granted <= 50) ` - Foreign keys are used where possibly to ensure referential integrity - Check the combinations of fields are valid ## Example without strict checks ```sql create table charge_point( id serial, country_code text, description text, peak_watthours numeric, serial_number text ) ``` with stricter checks: ```sql create table charge_point( id serial primary key, -- using standardized country codes country_code text references iso_country_code(country_code), -- validate against empty looking fields description text check(description is null or length(description)>=1), -- validate against expected ranges peak_watthours numeric check (peak_watthours between 0 and 1000), -- encode any business values, such as peak is lower for check(country_code = 'NL' and peak_watthours < 50), -- encode any expected value serial_number text check (serial_number ~ '^CP') ) ``` ## Some thoughts - unexpected state could be caused by a bug present easier in the process, such as user-input or unexpected input from an external service - could also prevent unintended changes when during database migrations or ad-hoc SQL scripts - strict checks seems like a low-hanging fruit that could be loosened anytime later, that's easier than make a loose schema more strict - maybe not all domain rules could be checks with db constraints, but it seems better to have something as compared to nothing - trailing newline can [cause issues in ETL and business intelligence](https://marcel.is/how-newline-can-ruin-your-hive/) use-cases - could reveal invalid data-entries, that should be captured by the frontend - if the in-place check function becomes complicated, one can consider encapsulating it into a database function > Data models are perhaps the most important part of developing software, because they have such a profound effect: not only on how the software is written, but also on how we think about the problem that we are solving. > —Designing Data Intensive Applications > Bugs are least expensive to avoid in design. Not in implementation, not in type systems. > —Rick Hickey, Hammock Driven Development > You can also give the constraint a separate name. This clarifies error messages and allows you to refer to the constraint when you need to change it > — [PostgreSQL: Documentation: 9.4: Constraints](https://www.postgresql.org/docs/9.4/ddl-constraints.html) ## Another example: Philadelphia spelling [https://twitter.com/dataeditor/status/1280278987797942272](https://twitter.com/dataeditor/status/1280278987797942272) ![[Pasted image 20220425171504.png]]