4 ms·

The relational model is a logical model; that it's backed up by a B-tree or something else should bear no consequence except in query/update performance. And y

The relational model is a logical model; that it's backed up by a B-tree or something else should bear no consequence except in query/update performance. And you kinda can simulate the sum types, you just can't express it in the first-order way: instead of CREATE TYPE Color (Red, Green, Blue) CREATE TABLE MyTable(x Color, ...) you do CREATE TABLE MyTableRed(...) CREATE TABLE MyTableGreen(...) CREATE TABLE MyTableBlue(...) There is just no syntax to group these three tables together in all relevant queries/updates, and such skolemization is really quite painful to maintain manually.

alexvoda 5y ago
In what situation do you believe your solution would be better than to create a table Colors, insert the desired list of colors and have a FK from MyTable to Colors? The way I see it a single atribute relation is a sum type. Implemented in current imperfect systems as a single column table (maybe two column table if you insist on using surogate keys for various reasons)
- Joker_vD 5y ago
  Probably in the situation where the sum types have actual payloads, instead of being simple enums. For example, if it was type SHAPE = NONE | SQUARE(w, h INTEGER) | CIRCLE(r INTEGER) | TRIANGLE(a, b, c INTEGER) | BLOB(e SVG_STRING) In my solution it entails having TABLE WithShapeNone(...) TABLE WithShapeSquare(..., w, h INTEGER) TABLE WithShapeCircle(..., r INTEGER) TABLE WithShapeTriangle(..., a, b, c INTEGER) TABLE WithShapeBlob(..., e SVG_STRING) It seems I can't easily generalize your solution, so I won't strawman it. Then again, I think the proper solution would just be adding the sum types into the system. We could have "+" implemented as a 3-column table too, but why would we? Your notice of "only values can be parametrized. Identifiers can not" in the sibling comment is exactly what I was trying to express in these threads: the relational model is first-order, not second-order, so while you can theoretically model everything with it (logician have done it), it's ugly and impractical without some extensions. Better types for "ground" values is one such extension.
  - alexvoda 5y ago
    For one point I do also believe that adding sum types would be great. Even if the only benefit is eliminating 3-value-logic, it is a huge benefit. I also agree that while it can be modeled it is very ugly, though I wouldn't necessarily blame the relational model. The relational model is a modelling tool. The physical implementation doesn't need to be 1 to 1 with the model. Same as you rarely find pure OO or pure FP languages. Current DBMSs do also have plenty of solutions for the problem of non-homogenous data. Some allow XML and JSON columns on which you can also enforce schemas. SqlServer proposes sparse tables. PostgreSQL has arrays, enums and structs (and arrays of structs) as data types. But I do agree that sum types ammong other things would really be nice. As for your example I still believe it is incomplete, but I would like to discuss that in a separate thread.
  - alexvoda 5y ago
    You make a fair point about actual payloads. My example was illustrating a sum type of unit types, an enum. Do you care to conjure a more complete example of your solution? As @throwaway894345 said, I believe your solution is difficult to consume. I would like an example with something more concrete than MyTable. A theoretical elegant but currently impossible solution to consume your solution would require FKs pointing to views. A view called Shape that is a Union All (we know there are no common elements) over the 5 WithShape... tables. Other tables would point FKs to the PK of this View. Unfortunately, AFAIK there is no DBMS that supports FKs to Views. I do believe this is not really what you have in mind and I have a hunch about what you are trying to get at, but I believe the example is incomplete.
throwaway894345 5y ago
> The relational model is a logical model; that it's backed up by a B-tree or something else should bear no consequence except in query/update performance. Agreed, but some people have incorrectly argued in the past that introducing sum types would harm performance due to the underlying data structures, so I was trying to head-off that line of argumentation. > And you kinda can simulate the sum types, I'm not sure I follow your example. Your `Color` type is a product type, not a sum type. I've explored every workaround I could think of for sum types (and asked many colleagues, etc), and they all failed in one important respect or another (typically because the type system doesn't understand your "sum type").
- Joker_vD 5y ago
  No, it's a sum type. You have to add the constraints CONSTRAINT MyTableRed INTERSECT MyTableGreen IS EMPTY CONSTRAINT MyTableGreen INTERSECT MyTableBlue IS EMPTY CONSTRAINT MyTableBlue INTERSECT MyTableRed IS EMPTY but after that, I believe it works. It essentially replaces the original "... and the color is 'x'" predicate with three another predicates: "... and the color is red", "... and the color is green", "... and the color is blue" with the constraint that only one (at most) of those can be true for any particular value of "...". As you can see, you have to do that for every would-be COLOR property, and adding more variants blows up the number of tables and constraints even more drastically. It kinda reminds me of 3SAT and other logical exercises in reducing everything down to the first-order logic: it's doable, but the terms grow exponentially large.
  - throwaway894345 5y ago
    The problem IIRC is actually consuming that sum type in SQL. Unfortunately it's been several years since I played with that approach, and I've forgotten the specific problems I encountered.
    - alexvoda 5y ago
      The issue I see is that now you have mixed data and schema. And unfortunately altering schema is much more difficult in current systems. There is no homoiconic SQL DBMS. This lack of homoiconicity is aso reflected in the fact that only values can be parametrized. Identifiers can not. Also you face issues if there is some other entity depending on MyTable. You can no longer have a FK because you no longer have a table to which to point the FK, you have 3 tables. You could create a table containing just the key and have FKs pointing from the three tables (I think this pattern does have a name) but you run more issues because there is usually no way to force bidirectional mandatory optionality using FKs. (A FK can be 0..1 - 0..M, 1..1 - 0..M, 0..1 - 0..1 or 1..1 - 0..1. But it can never be 0..1 - 1..M, 1..1 - 1..M or 1..1 - 1..1. In this case 0..1 - 1..1 is not an issue because it can be reversed as 1..1 - 0..1. ) Now you have two options: - Either create FKs in both directions, but then you run into the issue that most DBMS can not update two tables at the same time in the same statement. You have to use transactions and defer FK checking until the end of the transaction. And by this point you have created a DSL over SQL. - Or you use extra flag columns in order to achive a three step process (insert key in key table, insert extra attributes in other table, update key table to mark as active) and enforce everything using constraints and triggers, but similarly, by this point you have created a DSL over SQL.

alexvoda 5y ago

In what situation do you believe your solution would be better than to create a table Colors, insert the desired list of colors and have a FK from MyTable to Colors? The way I see it a single atribute relation is a sum type. Implemented in current imperfect systems as a single column table (maybe two column table if you insist on using surogate keys for various reasons)

Joker_vD 5y ago
Probably in the situation where the sum types have actual payloads, instead of being simple enums. For example, if it was type SHAPE = NONE | SQUARE(w, h INTEGER) | CIRCLE(r INTEGER) | TRIANGLE(a, b, c INTEGER) | BLOB(e SVG_STRING) In my solution it entails having TABLE WithShapeNone(...) TABLE WithShapeSquare(..., w, h INTEGER) TABLE WithShapeCircle(..., r INTEGER) TABLE WithShapeTriangle(..., a, b, c INTEGER) TABLE WithShapeBlob(..., e SVG_STRING) It seems I can't easily generalize your solution, so I won't strawman it. Then again, I think the proper solution would just be adding the sum types into the system. We could have "+" implemented as a 3-column table too, but why would we? Your notice of "only values can be parametrized. Identifiers can not" in the sibling comment is exactly what I was trying to express in these threads: the relational model is first-order, not second-order, so while you can theoretically model everything with it (logician have done it), it's ugly and impractical without some extensions. Better types for "ground" values is one such extension.
- alexvoda 5y ago
  For one point I do also believe that adding sum types would be great. Even if the only benefit is eliminating 3-value-logic, it is a huge benefit. I also agree that while it can be modeled it is very ugly, though I wouldn't necessarily blame the relational model. The relational model is a modelling tool. The physical implementation doesn't need to be 1 to 1 with the model. Same as you rarely find pure OO or pure FP languages. Current DBMSs do also have plenty of solutions for the problem of non-homogenous data. Some allow XML and JSON columns on which you can also enforce schemas. SqlServer proposes sparse tables. PostgreSQL has arrays, enums and structs (and arrays of structs) as data types. But I do agree that sum types ammong other things would really be nice. As for your example I still believe it is incomplete, but I would like to discuss that in a separate thread.
- alexvoda 5y ago
  You make a fair point about actual payloads. My example was illustrating a sum type of unit types, an enum. Do you care to conjure a more complete example of your solution? As @throwaway894345 said, I believe your solution is difficult to consume. I would like an example with something more concrete than MyTable. A theoretical elegant but currently impossible solution to consume your solution would require FKs pointing to views. A view called Shape that is a Union All (we know there are no common elements) over the 5 WithShape... tables. Other tables would point FKs to the PK of this View. Unfortunately, AFAIK there is no DBMS that supports FKs to Views. I do believe this is not really what you have in mind and I have a hunch about what you are trying to get at, but I believe the example is incomplete.

throwaway894345 5y ago

> The relational model is a logical model; that it's backed up by a B-tree or something else should bear no consequence except in query/update performance. Agreed, but some people have incorrectly argued in the past that introducing sum types would harm performance due to the underlying data structures, so I was trying to head-off that line of argumentation. > And you kinda can simulate the sum types, I'm not sure I follow your example. Your `Color` type is a product type, not a sum type. I've explored every workaround I could think of for sum types (and asked many colleagues, etc), and they all failed in one important respect or another (typically because the type system doesn't understand your "sum type").

Joker_vD 5y ago
No, it's a sum type. You have to add the constraints CONSTRAINT MyTableRed INTERSECT MyTableGreen IS EMPTY CONSTRAINT MyTableGreen INTERSECT MyTableBlue IS EMPTY CONSTRAINT MyTableBlue INTERSECT MyTableRed IS EMPTY but after that, I believe it works. It essentially replaces the original "... and the color is 'x'" predicate with three another predicates: "... and the color is red", "... and the color is green", "... and the color is blue" with the constraint that only one (at most) of those can be true for any particular value of "...". As you can see, you have to do that for every would-be COLOR property, and adding more variants blows up the number of tables and constraints even more drastically. It kinda reminds me of 3SAT and other logical exercises in reducing everything down to the first-order logic: it's doable, but the terms grow exponentially large.
- throwaway894345 5y ago
  The problem IIRC is actually consuming that sum type in SQL. Unfortunately it's been several years since I played with that approach, and I've forgotten the specific problems I encountered.
  - alexvoda 5y ago
    The issue I see is that now you have mixed data and schema. And unfortunately altering schema is much more difficult in current systems. There is no homoiconic SQL DBMS. This lack of homoiconicity is aso reflected in the fact that only values can be parametrized. Identifiers can not. Also you face issues if there is some other entity depending on MyTable. You can no longer have a FK because you no longer have a table to which to point the FK, you have 3 tables. You could create a table containing just the key and have FKs pointing from the three tables (I think this pattern does have a name) but you run more issues because there is usually no way to force bidirectional mandatory optionality using FKs. (A FK can be 0..1 - 0..M, 1..1 - 0..M, 0..1 - 0..1 or 1..1 - 0..1. But it can never be 0..1 - 1..M, 1..1 - 1..M or 1..1 - 1..1. In this case 0..1 - 1..1 is not an issue because it can be reversed as 1..1 - 0..1. ) Now you have two options: - Either create FKs in both directions, but then you run into the issue that most DBMS can not update two tables at the same time in the same statement. You have to use transactions and defer FK checking until the end of the transaction. And by this point you have created a DSL over SQL. - Or you use extra flag columns in order to achive a three step process (insert key in key table, insert extra attributes in other table, update key table to mark as active) and enforce everything using constraints and triggers, but similarly, by this point you have created a DSL over SQL.