3 ms·

I often wonder about this kind of solution compared with a more traditional solution of using a table of columns and constraints. There seems like there should

I often wonder about this kind of solution compared with a more traditional solution of using a table of columns and constraints. There seems like there should be an obvious heuristic of when to use one or the other but it eludes me.

oliverrice 4y ago
Definitely. For the use-cases shown in the blog post, a table would be equally valid (and easier). The heuristic I use is to create custom types when the values will need to be used by SQL functions, or if support for things like ordering/aggregating are important to the application, and the rules to support those things require custom logic. For example, SemVer requires that two version that differ only in metadata e.g. `1.0.0+metaA` and `1.0.0+metaB` should be considered equal. It would be error prone and tedious to push that logic onto every query that wanted to sort the table, but with a type we can define the logic once and have it work everywhere.
ericHosick 4y ago
A good heuristic to consider when designing composite types is whether any part of the type would lead to redundant storage. For example, a currency composite type might consist of an amount field and a type field. In most cases, a single invoice, order, deal, etc. is unlikely to be generated using multiple currencies. Therefore, a single currency_type field in the invoice table would be sufficient. If we used a composite currency type, the currency_type field would be highly redundant.
- skissane 4y ago
  > If we used a composite currency type, the currency_type field would be highly redundant. It can help prevent the bug in which someone adds up multiple currencies without conversion. “25 USD + 25 EUR” will either fail or work via some currency conversion routine. “25 + 25” will produce a meaningless and useless wrong answer.
  - doctor_eval 4y ago
    Yes, this is exactly what I’ve done previously. For example, I implemented a sum() aggregate function that took “monetary” (currency + value) composite types and raised an exception if the currencies were not all the same.

oliverrice 4y ago

Definitely. For the use-cases shown in the blog post, a table would be equally valid (and easier). The heuristic I use is to create custom types when the values will need to be used by SQL functions, or if support for things like ordering/aggregating are important to the application, and the rules to support those things require custom logic. For example, SemVer requires that two version that differ only in metadata e.g. `1.0.0+metaA` and `1.0.0+metaB` should be considered equal. It would be error prone and tedious to push that logic onto every query that wanted to sort the table, but with a type we can define the logic once and have it work everywhere.

ericHosick 4y ago

A good heuristic to consider when designing composite types is whether any part of the type would lead to redundant storage. For example, a currency composite type might consist of an amount field and a type field. In most cases, a single invoice, order, deal, etc. is unlikely to be generated using multiple currencies. Therefore, a single currency_type field in the invoice table would be sufficient. If we used a composite currency type, the currency_type field would be highly redundant.

skissane 4y ago
> If we used a composite currency type, the currency_type field would be highly redundant. It can help prevent the bug in which someone adds up multiple currencies without conversion. “25 USD + 25 EUR” will either fail or work via some currency conversion routine. “25 + 25” will produce a meaningless and useless wrong answer.
- doctor_eval 4y ago
  Yes, this is exactly what I’ve done previously. For example, I implemented a sum() aggregate function that took “monetary” (currency + value) composite types and raised an exception if the currencies were not all the same.