8 ms·

My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restru

My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.

mdavid626 1y ago
In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.
- AdrianB1 1y ago
  The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
  - mdavid626 1y ago
    This is and “old-school” design. Nowadays I wouldn’t let apps meet in the database. Simple service oriented architecture is much preferred. Each app with its own data. Then such problems can be easily avoided.
    - dakiol 1y ago
      It’s not old school, it’s actually solid design. I have worked too with people that think the frontend or even services should guide the design/architecture of the whole thing. Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. Having Data structures (and mainly this means database structures) stable is key to long term maintenance.
      - radlad 1y ago
        > Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. This appears as an opinion rather than an argument. Could you explain what you find bad about the design? In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements.
        dakiol 1y ago
        It's an opinion based on countless of references and books out there. I cannot cite them, but it's like "code should be designed to depend on abstract interfaces instead of a concrete implementation", "everything is a byte stream", "adding more people to a late project makes it later", "Bad programmers worry about the code. Good programmers worry about data structures and their relationships", "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious.", etc... they are usually true.
        RaftPeople 1y ago
        > In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements. I think the idea of breaking up a shared enterprise DB into many distinct but communicating and dependent DB's was driven by a desire to reduce team+system dependencies to increase ability to change. While the pro is valid and we make use of the idea sometimes when we design things, the cons are significant. Splitting up a DB that has data that is naturally shared by many departments in the business and by many modules/functional areas of the system increases complexity substantially. In the shared model, when some critical attribute of an item (sku) is updated, then all of the different modules+functional areas of enterprise are immediately using that current and correct master value. In the distributed model, there is significant complexity and effort to share this state across all areas. I've worked on systems designed this way and this issue frequently causes problems related to timing. As with everything, no single solution is best for all situations. We only split this kind of shared state when the pros outweigh the cons, which is sometimes but not that often.
mdavid626 1y ago
Good, simple solution could be data duplication, eg. store some props from the joined tables directly in the main table. I know, for many, this is one of the deadly sins, but I think it can work out very well.

mdavid626 1y ago

In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.

AdrianB1 1y ago
The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
- mdavid626 1y ago
  This is and “old-school” design. Nowadays I wouldn’t let apps meet in the database. Simple service oriented architecture is much preferred. Each app with its own data. Then such problems can be easily avoided.
  - dakiol 1y ago
    It’s not old school, it’s actually solid design. I have worked too with people that think the frontend or even services should guide the design/architecture of the whole thing. Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. Having Data structures (and mainly this means database structures) stable is key to long term maintenance.
    - radlad 1y ago
      > Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. This appears as an opinion rather than an argument. Could you explain what you find bad about the design? In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements.
      - dakiol 1y ago
        It's an opinion based on countless of references and books out there. I cannot cite them, but it's like "code should be designed to depend on abstract interfaces instead of a concrete implementation", "everything is a byte stream", "adding more people to a late project makes it later", "Bad programmers worry about the code. Good programmers worry about data structures and their relationships", "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious.", etc... they are usually true.
      - RaftPeople 1y ago
        > In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements. I think the idea of breaking up a shared enterprise DB into many distinct but communicating and dependent DB's was driven by a desire to reduce team+system dependencies to increase ability to change. While the pro is valid and we make use of the idea sometimes when we design things, the cons are significant. Splitting up a DB that has data that is naturally shared by many departments in the business and by many modules/functional areas of the system increases complexity substantially. In the shared model, when some critical attribute of an item (sku) is updated, then all of the different modules+functional areas of enterprise are immediately using that current and correct master value. In the distributed model, there is significant complexity and effort to share this state across all areas. I've worked on systems designed this way and this issue frequently causes problems related to timing. As with everything, no single solution is best for all situations. We only split this kind of shared state when the pros outweigh the cons, which is sometimes but not that often.

mdavid626 1y ago

Good, simple solution could be data duplication, eg. store some props from the joined tables directly in the main table. I know, for many, this is one of the deadly sins, but I think it can work out very well.