5 ms·

There are databases (and data warehouses) that need to provide random query api’s, e.g, for reporting, analytics, etc. Databases can be considered to provide an

by ako 3y ago

There are databases (and data warehouses) that need to provide random query api’s, e.g, for reporting, analytics, etc. Databases can be considered to provide an api layer themselves, way more flexible than most standards APIs provide. Graphql and odata go a long way to solve this. Also, database views can provide a data api layer for a database. Views provide a stable datamodel, that allow the underlying tables to be changed.

refset 3y ago
Views seem like the ideal solution in theory, but SQL implementations of views are often problematic in practice due to planning complexity & overhead. In theory they should also be a good mechanism for handling writes (see "updateable views" / "writable views") but the list of caveats is long and many developers are understandably nervous about pushing lots of logic into TRIGGERs.
contravariant 3y ago
I'm inclined to agree, but just to provide a counterpoint: if the data is modelled properly then there are very few reporting queries that actually make sense. Except of course if you want people to be able to change definitions on the fly, but I'd argue that is an anti-feature when it comes to reporting.
- hosh 3y ago
  Data gets messy, because the real world is messy. Once you start ingesting from other sources, it gets very messy. There is a reason ideas like the "data mesh" or semantic web (distributed schemas) were attempted.
  - contravariant 3y ago
    Sure, but that complexity is something you do not want to deal with in your reporting API layer. A reporting API should report correct figures, you don't want to put the responsibility for ensuring the numbers add up on the person requesting the report.
    - hosh 3y ago
      Beware the fallacy of legibility. https://www.ribbonfarm.com/2010/07/26/a-big-little-idea-called-legibility/ https://www.ribbonfarm.com/2010/07/26/a-big-little-idea-call...
      - contravariant 3y ago
        Surely communication is the one area where it's fairly safe to apply the concept of legibility?
        hosh 3y ago
        It's already bad enough that people, by default, are not aware of the frame of reference in which they see the world, let alone communicate with. As just a very simple example, unless I am explicitly clear about speaking from a Living Systems world view and a regenerative paradigm, when I talk about permaculture design here in this forum, I end up talking past the vast majority of people here on HN. But even staying with the same shared technological world views and paradigms -- the people across the Three Tribes of Programmers (https://josephg.com/blog/3-tribes/ https://josephg.com/blog/3-tribes/) will get into flamewars because the frame differs in distinct ways. Because a schema makes distinctions on information in a particular way, it will always encode a frame in which to view and understand that information.
        contravariant 3y ago
        This is turning into a rather interesting discussion, but surely it would be better to decide on a schema with each other (or perhaps multiple schemas if absolutely necessary), as opposed to just giving people the ability to query whatever they want and present the results? If the query itself cannot be explained simply then what does its output even communicate? The more freedom you put in the API the more room you leave for confusion, this might be appropriate for research but not when you're reporting on something.
        3y ago

by ako 3y ago

refset 3y ago

Views seem like the ideal solution in theory, but SQL implementations of views are often problematic in practice due to planning complexity & overhead. In theory they should also be a good mechanism for handling writes (see "updateable views" / "writable views") but the list of caveats is long and many developers are understandably nervous about pushing lots of logic into TRIGGERs.

contravariant 3y ago

I'm inclined to agree, but just to provide a counterpoint: if the data is modelled properly then there are very few reporting queries that actually make sense. Except of course if you want people to be able to change definitions on the fly, but I'd argue that is an anti-feature when it comes to reporting.

hosh 3y ago
Data gets messy, because the real world is messy. Once you start ingesting from other sources, it gets very messy. There is a reason ideas like the "data mesh" or semantic web (distributed schemas) were attempted.
- contravariant 3y ago
  Sure, but that complexity is something you do not want to deal with in your reporting API layer. A reporting API should report correct figures, you don't want to put the responsibility for ensuring the numbers add up on the person requesting the report.
  - hosh 3y ago
    Beware the fallacy of legibility. https://www.ribbonfarm.com/2010/07/26/a-big-little-idea-called-legibility/ https://www.ribbonfarm.com/2010/07/26/a-big-little-idea-call...
    - contravariant 3y ago
      Surely communication is the one area where it's fairly safe to apply the concept of legibility?
      - hosh 3y ago
        It's already bad enough that people, by default, are not aware of the frame of reference in which they see the world, let alone communicate with. As just a very simple example, unless I am explicitly clear about speaking from a Living Systems world view and a regenerative paradigm, when I talk about permaculture design here in this forum, I end up talking past the vast majority of people here on HN. But even staying with the same shared technological world views and paradigms -- the people across the Three Tribes of Programmers (https://josephg.com/blog/3-tribes/ https://josephg.com/blog/3-tribes/) will get into flamewars because the frame differs in distinct ways. Because a schema makes distinctions on information in a particular way, it will always encode a frame in which to view and understand that information.
        contravariant 3y ago
        This is turning into a rather interesting discussion, but surely it would be better to decide on a schema with each other (or perhaps multiple schemas if absolutely necessary), as opposed to just giving people the ability to query whatever they want and present the results? If the query itself cannot be explained simply then what does its output even communicate? The more freedom you put in the API the more room you leave for confusion, this might be appropriate for research but not when you're reporting on something.
        3y ago