5 ms·

The relational model describes a relational algebra, which in turn specifies a "relation" (often called a table) to be a set of tuples (rows), and "relational o

by ben509 5y ago

The relational model describes a relational algebra, which in turn specifies a "relation" (often called a table) to be a set of tuples (rows), and "relational operators" that accept and return relations. And the relational algebra is complete in the sense that it can express all queries expressible by predicate logic + types and changes through a small set of operations. The relational model adds constraints, state, and a mechanism for first-class derived relations (updateable views). And while you can stick anything with a well-defined equality in a relation, including other relations, the point of the relational algebra is to describe structure using relations, thus making it all accessible to relational operators. In a properly normalized database, all structure can be manipulated through a common set of operations. So you could, e.g. create a relation with a single JSON attribute and call it a day. But now, in addition to the relational operators, you need a whole mess of JSON operators to query it. Thus, while you could have a sum type, you don't need this because you can put the various summands into separate relations. For instance, the simple case of booleans: Persons(key id: int, name: str, is_tall: bool) ... noramlizes to ... Persons(key id: int, name: str) TallPersons(key id: int) Or for an Either: Persons(key id: int, name: str, zing: either<int, str>) ... noramlizes to ... Persons(key id: int, name: str) LeftPersons(key id: int, zing: int) RightPersons(key id: int, zing: str) AssertEmpty: LeftPersons{key} & RightPersons{key} What you really want your database to do is to let you enter that first "Persons" table with the sum type. That should logically be a derived table that is backed by the normalized tables. Then, you'd get the simplicity of entering Persons.insert(key=5, name='bob', zing=Left(5)), but that's simply an updateable view. It will really update the base tables Persons / LeftPersons with simple atomic values.

hardwaregeek 5y ago
That's a very good explanation, thanks. However I'd argue that just because you can express something in a language, doesn't mean it's the optimal way to express it. Any turing complete language can express sum types, but there's something to be said about having explicit features within the language for sum types. The SQL vs NoSQL debate reminds me of the static types vs dynamic types debate. Static type systems offer a more robust mental model and verification, but at the cost of rigidity and forcing the programmer to think in the type system's mental model. Dynamic types are therefore appealing because they can adapt to the programmer's mental model, even if that model is seriously flawed. One change in philosophy that has helped static type system is the realization that you can design static type systems that while not conceptually pure, get closer to the dynamic mental models. These systems, such as TypeScript or Go, make static types easier to swallow. I wonder if the same could be said for SQL/relational systems. If you could design a relational system that's a touch more ergonomic, that doesn't require learning a mental model that feels a little foreign, maybe the NoSQL options will be a lot less appealing.
- ben509 5y ago
  > However I'd argue that just because you can express something in a language, doesn't mean it's the optimal way to express it. I completely agree, and I may not have driven this point home, but because a relational system lets you have different views of the same data, you can have sum types in one view, while breaking them into relational structures in another view of the same data. We don't get that in SQL DBMSs because SQL isn't really relational. > If you could design a relational system that's a touch more ergonomic, that doesn't require learning a mental model that feels a little foreign, maybe the NoSQL options will be a lot less appealing. What's could be ergonomic about a strongly system is that it can guarantee that if I pull a record from the database, it has exactly what types it says it does, so my code knows what it's dealing with. Even if I'm writing something in Python, I don't actually want to do a mess of instanceof checks. I think what we want is to properly wire a DBMS into build tools. The production system should have everything strictly typed, presenting a clean API to consumers. Meanwhile, a development branch should let you put whatever you want in there while you're experimenting, and then you lock it down before you promote your code to prod.
gugagore 5y ago
I've skimmed https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization before and I'm trying to reconcile your clear explanation with what is there. > Informally, a relational database relation is often described as "normalized" if it meets third normal form. So then I look at https://en.wikipedia.org/wiki/Third_normal_form https://en.wikipedia.org/wiki/Third_normal_form , which has this fun: > An approximation of Codd's definition of 3NF, paralleling the traditional pledge to give true evidence in a court of law, was given by Bill Kent: "[every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key".[7] A common variation supplements this definition with the oath "so help me Codd". I don't see how that relates to your normalization. It's possible the simple case of booleans was just for illustration, but if not, then it suggests to me that there should never be any boolean column in the normalized schema, since you can have an additional table containing the keys corresponding to e.g. `true` values. Could anyone clarify?
- ben509 5y ago
  So 2NF and 3NF are rules that aim to eliminate data redundancies. That's Kent's point: everything is a fact about a key. I'm talking about a more basic idea of expressing structures that are accessible through relational operations, so joins, intersections, unions, etc. That's known as 1NF[1] and, as you might expect, it's a pre-requisite to 2NF and 3NF. [1]: https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form
  - gugagore 5y ago
    Thanks. And so in 1NF, would you ever have a column of type boolean?
    - ben509 5y ago
      Nope. I believe that's why ANSI SQL doesn't specify a boolean column type.
layer8 5y ago
The problem is that you will want to query all persons with (say) a particular name regardless of the zing, and maybe join it with some related table (say, Address), but still want (as the client application) to receive the zing values. To represent the result with a single relation, you need (in the result) nullable fields for left zing and right zing, and then it makes more sense to have those in Person in the first place (plus a constraint that they are exclusive). The only alternative would be that the client receives several on-the-fly created relations as the result (i.e. LeftPersonsWithAddress and RightPersonsWithAddress), but then what about a sorted result? E.g. the client may want all resulting PersonsWithAddress sorted by ZIP code.
phaedrus 5y ago
What prevents this same line of reasoning from being used to justify that SQL does not need a DATETIME primitive type? Just as you showed how a sum type could be represented with component relations, a similar example could be constructed showing how any needed attribute of a datetime could be normalized to various tables combining the features of dates and times.

by ben509 5y ago

hardwaregeek 5y ago

That's a very good explanation, thanks. However I'd argue that just because you can express something in a language, doesn't mean it's the optimal way to express it. Any turing complete language can express sum types, but there's something to be said about having explicit features within the language for sum types. The SQL vs NoSQL debate reminds me of the static types vs dynamic types debate. Static type systems offer a more robust mental model and verification, but at the cost of rigidity and forcing the programmer to think in the type system's mental model. Dynamic types are therefore appealing because they can adapt to the programmer's mental model, even if that model is seriously flawed. One change in philosophy that has helped static type system is the realization that you can design static type systems that while not conceptually pure, get closer to the dynamic mental models. These systems, such as TypeScript or Go, make static types easier to swallow. I wonder if the same could be said for SQL/relational systems. If you could design a relational system that's a touch more ergonomic, that doesn't require learning a mental model that feels a little foreign, maybe the NoSQL options will be a lot less appealing.

ben509 5y ago
> However I'd argue that just because you can express something in a language, doesn't mean it's the optimal way to express it. I completely agree, and I may not have driven this point home, but because a relational system lets you have different views of the same data, you can have sum types in one view, while breaking them into relational structures in another view of the same data. We don't get that in SQL DBMSs because SQL isn't really relational. > If you could design a relational system that's a touch more ergonomic, that doesn't require learning a mental model that feels a little foreign, maybe the NoSQL options will be a lot less appealing. What's could be ergonomic about a strongly system is that it can guarantee that if I pull a record from the database, it has exactly what types it says it does, so my code knows what it's dealing with. Even if I'm writing something in Python, I don't actually want to do a mess of instanceof checks. I think what we want is to properly wire a DBMS into build tools. The production system should have everything strictly typed, presenting a clean API to consumers. Meanwhile, a development branch should let you put whatever you want in there while you're experimenting, and then you lock it down before you promote your code to prod.

gugagore 5y ago

I've skimmed https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization before and I'm trying to reconcile your clear explanation with what is there. > Informally, a relational database relation is often described as "normalized" if it meets third normal form. So then I look at https://en.wikipedia.org/wiki/Third_normal_form https://en.wikipedia.org/wiki/Third_normal_form , which has this fun: > An approximation of Codd's definition of 3NF, paralleling the traditional pledge to give true evidence in a court of law, was given by Bill Kent: "[every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key".[7] A common variation supplements this definition with the oath "so help me Codd". I don't see how that relates to your normalization. It's possible the simple case of booleans was just for illustration, but if not, then it suggests to me that there should never be any boolean column in the normalized schema, since you can have an additional table containing the keys corresponding to e.g. `true` values. Could anyone clarify?

ben509 5y ago
So 2NF and 3NF are rules that aim to eliminate data redundancies. That's Kent's point: everything is a fact about a key. I'm talking about a more basic idea of expressing structures that are accessible through relational operations, so joins, intersections, unions, etc. That's known as 1NF[1] and, as you might expect, it's a pre-requisite to 2NF and 3NF. [1]: https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form
- gugagore 5y ago
  Thanks. And so in 1NF, would you ever have a column of type boolean?
  - ben509 5y ago
    Nope. I believe that's why ANSI SQL doesn't specify a boolean column type.

layer8 5y ago

The problem is that you will want to query all persons with (say) a particular name regardless of the zing, and maybe join it with some related table (say, Address), but still want (as the client application) to receive the zing values. To represent the result with a single relation, you need (in the result) nullable fields for left zing and right zing, and then it makes more sense to have those in Person in the first place (plus a constraint that they are exclusive). The only alternative would be that the client receives several on-the-fly created relations as the result (i.e. LeftPersonsWithAddress and RightPersonsWithAddress), but then what about a sorted result? E.g. the client may want all resulting PersonsWithAddress sorted by ZIP code.

phaedrus 5y ago

What prevents this same line of reasoning from being used to justify that SQL does not need a DATETIME primitive type? Just as you showed how a sum type could be represented with component relations, a similar example could be constructed showing how any needed attribute of a datetime could be normalized to various tables combining the features of dates and times.