3 ms·

> ORMs attempt to deal with that mismatch. Technically ORMs attempt to deal with the mismatch between relations[1] and the rich data structures (objects) gener

by 9rx 1y ago

> ORMs attempt to deal with that mismatch. Technically ORMs attempt to deal with the mismatch between relations[1] and the rich data structures (objects) general purpose programming languages normally allow expression of. Hence the literal name: Object-Relation Mapping. That SQL, the language, is declarative is immaterial. > but Codd's goals when developing the relational model was to allow non programmers to access data. That is unlikely. He was staunchly opposed to database vendors adding "veneer" to make use more friendly and considered SQL an abomination. Codd was dead set on mathematical purity, which is, I'd argue, also why his vision ultimately failed in practice as actual relational databases are too hard to understand for those not well versed in the math. [1] Technically tables, since we're talking about SQL, which isn't relational. But the mapping idea is the same either way.

reaanb2 1y ago
"Object-relational mapper" is a misnomer, they should be called network data model to SQL mappers. They're not primarily for mapping between different arrangements of the same data, but about recreating a network data model perspective. How many ORMs support composite primary keys and non-binary relationships? As you said, the challenge of understanding math is why the relational vision failed, but also why the industry keeps reviving the network data model.
- 9rx 1y ago
  > How many ORMs support composite primary keys and non-binary relationships? Theoretically all. However, often ORM and query building are combined into a unified framework that links them together into a higher order abstraction. You may know this type of thing by names like the active record pattern or the data mapper pattern, with concrete implementations like ActiveRecord and Entity Framework. Such frameworks often make it difficult to use the ORM component in isolation (and even where that isn't the case, doing so largely defeats the purpose of using the framework anyway), and as such you become constrained by the limits of the query builder. I suspect that is what you are really thinking of.
  - reaanb2 1y ago
    EF is limited to binary relationships: https://learn.microsoft.com/en-us/ef/core/modeling/relationships https://learn.microsoft.com/en-us/ef/core/modeling/relations.... When entities are mapped to rows and relationships to foreign key constraints, it results in a network data model interpretation of SQL databases. The relational model is better conceptualized from a fact-oriented modeling perspective, in which entities map to values, domains to FK constraints, relationships to the association of two or more entity sets in a table, and rows represent propositions about entities rather than entities themselves. However, non-binary relationships don't easily translate into navigational data access (cf. Bachman's paper "The programmer as navigator" about the network data model) and a logical view is difficult for the non-mathematical programmer, while structs and pointers are naïvely easy to understand, that's why ORMs remain popular. The impedance mismatch is less about mapping flat to rich data structures than about the network data model vs fact-oriented perspectives and row-at-a-time vs batch data access.
    - 9rx 1y ago
      > EF is limited to binary relationships But due to the constraints of its querying abilities, not the ORM aspect. ORM is only concerned with the data once it is materialized. How you materialize that data, be it the product of a network relationship, or a fact-oriented model, is immaterial. Remember, EF isn't just an ORM toolkit. It is an ORM toolkit plus a number of other features. But since we're only talking about ORM those other features don't relate to the discussion. > while structs and pointers are naïvely easy to understand, that's why ORMs remain popular. And, well, because it is an all-round good idea to turn externally-sourced data models into internal data models anyway. It makes life a hell a lot easier as things start to change over time. Even if you were to receive serialized data that is shaped exactly in the structure you want it at a given moment in time, you still would want to go through a mapping process to provide the clean break from someone else's data model and your own. This isn't limited to SQL database tables. You'd do the same if you received, say, JSON from a web service.
nyrikki 1y ago
The 'relation' in the relational model is the tables, specific named columns and tuples, with specific abstracts [0] The relation is the table, normalization, foreign keys, candidate keys etc are all extensions to that base model for Codd. Some of the impedance mismatch is due to that, and not just the declarative nature or extensions Specifically I think the Alice book covers how with a candidate key + the remaining tuples in the row form the row but only the candidate key has an identity, the rest of the row is a substring. Some quotes from the link, but searching for 'user' will hit what I think justifies my interpretation. > Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation). > To sum up, it is proposed that most users should interact with a relational model of the data consisting of a collection of time-varying relationships (rather than relations). Each user need not know more about any relationship than its name together with the names of its domains (role qualified whenever necessary): Even this information might be offered in menu style by the system (subject to security and privacy constraints) upon request by the user. [0] https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
- 9rx 1y ago
  > The 'relation' in the relational model is the tables No. A table is a list/multiset of tuples, while a relation is a set of tuples. If you squint hard enough they might look similar, but they are not the same. The relational model has no concept of tables. > Some of the impedance mismatch is due to that ORM doesn't really have an impedance mismatch in and of itself. It is just a data transformation. The impedance mismatch that is oft spoken of in association with ORM revolves around the N+1 problem. This is where you get some bizarreness that has to lean on hacks to overcome the real-world constraints that the mathematical model doesn't account for. If you are using something like SQLite that isn't so much of a problem in practice, of course.
  - nyrikki 1y ago
    In first order predicate logic with an n-ary relation, attributes are columns and tuples are rows aka a table and what Codd used to justify it. The typical normalization let's say in a star schema is viewable as a least fixed point. FO+LFP=P It is similar to what Codd called adjacency lists, which luckily died in the 80s, because they conflicted with real adjacency lists. recursive CTEs add transitive closure, which when added to FO gets you to L or NL, I forget which . Still it doesn't matter, the relational part of the relational model is attributes or column names...it is equivalent.

by 9rx 1y ago

reaanb2 1y ago

"Object-relational mapper" is a misnomer, they should be called network data model to SQL mappers. They're not primarily for mapping between different arrangements of the same data, but about recreating a network data model perspective. How many ORMs support composite primary keys and non-binary relationships? As you said, the challenge of understanding math is why the relational vision failed, but also why the industry keeps reviving the network data model.

9rx 1y ago
> How many ORMs support composite primary keys and non-binary relationships? Theoretically all. However, often ORM and query building are combined into a unified framework that links them together into a higher order abstraction. You may know this type of thing by names like the active record pattern or the data mapper pattern, with concrete implementations like ActiveRecord and Entity Framework. Such frameworks often make it difficult to use the ORM component in isolation (and even where that isn't the case, doing so largely defeats the purpose of using the framework anyway), and as such you become constrained by the limits of the query builder. I suspect that is what you are really thinking of.
- reaanb2 1y ago
  EF is limited to binary relationships: https://learn.microsoft.com/en-us/ef/core/modeling/relationships https://learn.microsoft.com/en-us/ef/core/modeling/relations.... When entities are mapped to rows and relationships to foreign key constraints, it results in a network data model interpretation of SQL databases. The relational model is better conceptualized from a fact-oriented modeling perspective, in which entities map to values, domains to FK constraints, relationships to the association of two or more entity sets in a table, and rows represent propositions about entities rather than entities themselves. However, non-binary relationships don't easily translate into navigational data access (cf. Bachman's paper "The programmer as navigator" about the network data model) and a logical view is difficult for the non-mathematical programmer, while structs and pointers are naïvely easy to understand, that's why ORMs remain popular. The impedance mismatch is less about mapping flat to rich data structures than about the network data model vs fact-oriented perspectives and row-at-a-time vs batch data access.
  - 9rx 1y ago
    > EF is limited to binary relationships But due to the constraints of its querying abilities, not the ORM aspect. ORM is only concerned with the data once it is materialized. How you materialize that data, be it the product of a network relationship, or a fact-oriented model, is immaterial. Remember, EF isn't just an ORM toolkit. It is an ORM toolkit plus a number of other features. But since we're only talking about ORM those other features don't relate to the discussion. > while structs and pointers are naïvely easy to understand, that's why ORMs remain popular. And, well, because it is an all-round good idea to turn externally-sourced data models into internal data models anyway. It makes life a hell a lot easier as things start to change over time. Even if you were to receive serialized data that is shaped exactly in the structure you want it at a given moment in time, you still would want to go through a mapping process to provide the clean break from someone else's data model and your own. This isn't limited to SQL database tables. You'd do the same if you received, say, JSON from a web service.

nyrikki 1y ago

The 'relation' in the relational model is the tables, specific named columns and tuples, with specific abstracts [0] The relation is the table, normalization, foreign keys, candidate keys etc are all extensions to that base model for Codd. Some of the impedance mismatch is due to that, and not just the declarative nature or extensions Specifically I think the Alice book covers how with a candidate key + the remaining tuples in the row form the row but only the candidate key has an identity, the rest of the row is a substring. Some quotes from the link, but searching for 'user' will hit what I think justifies my interpretation. > Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation). > To sum up, it is proposed that most users should interact with a relational model of the data consisting of a collection of time-varying relationships (rather than relations). Each user need not know more about any relationship than its name together with the names of its domains (role qualified whenever necessary): Even this information might be offered in menu style by the system (subject to security and privacy constraints) upon request by the user. [0] https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

9rx 1y ago
> The 'relation' in the relational model is the tables No. A table is a list/multiset of tuples, while a relation is a set of tuples. If you squint hard enough they might look similar, but they are not the same. The relational model has no concept of tables. > Some of the impedance mismatch is due to that ORM doesn't really have an impedance mismatch in and of itself. It is just a data transformation. The impedance mismatch that is oft spoken of in association with ORM revolves around the N+1 problem. This is where you get some bizarreness that has to lean on hacks to overcome the real-world constraints that the mathematical model doesn't account for. If you are using something like SQLite that isn't so much of a problem in practice, of course.
- nyrikki 1y ago
  In first order predicate logic with an n-ary relation, attributes are columns and tuples are rows aka a table and what Codd used to justify it. The typical normalization let's say in a star schema is viewable as a least fixed point. FO+LFP=P It is similar to what Codd called adjacency lists, which luckily died in the 80s, because they conflicted with real adjacency lists. recursive CTEs add transitive closure, which when added to FO gets you to L or NL, I forget which . Still it doesn't matter, the relational part of the relational model is attributes or column names...it is equivalent.