3 ms·

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.

eddd-ddde 3y ago
I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative?
- arrowleaf 3y ago
  Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?
  - eddd-ddde 3y ago
    Because that single application already exists, in my case its postgres. Pgweb is one of my interfaces, admin dashboard for free, and I am sure the changes I make there are as valid as changes through any other interface. I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database... The database is that "single appplication", no need to write custom endpoints for every operation when SQL is good enough. It's all a spectrum of course, today you can even go as far as to use something like pg_graphql and you don't even need to write a REST api yourself. Edit: I forgot to answer the last section, but postgres can totally handle autorization with RLS for instance, allowing users to only see their own data, or maybe data marked as public, etc.
    - DasIch 3y ago
      If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it. It is not realistic that you can trust everyone who needs to access the data with access to the database as they might easily cause problems with poorly written queries. Additionally you may want invariants maintained, that the database cannot maintain but an application in front of it can. Also for historical data or analytical queries postgres is not ideal either, so you probably want to move the data into some OLAP database or datalake. > I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database... If the website and the scraper are just parts of the same application, it makes sense to do this but if they are genuinely different applications. I would use different databases here.
      - Akronymus 3y ago
        > If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it. I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far.
        JackFr 3y ago
        > I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far. I found such an environment to be simply terrible. In general, I think stored procs certainly have their place, but if ALL access is through stored procs, you better have a schema that’s basically set in stone otherwise dev will turn into a nightmare.
    - jrumbut 3y ago
      > Because that single application already exists, in my case its postgres. This is exactly what postgres was designed for! Tens of thousands of hours of work over decades to solve the problem of relational database management. That's why we call it an RDBMS! Which isn't to say you shouldn't make your own API ever. There are a lot of situations where you don't want things to connect directly to postgres. But you shouldn't be afraid of having multiple systems connect to postgres. It has incredibly mature and robust features to accommodate that use case. It's the expected use case.
butlerm 3y ago
Large ERP systems do that sort of thing as a matter of course and have for decades now. It does require careful planning and design. I mean AR / AP / scheduling / manufacturing / inventory and so on. The main downside of splitting everything into isolated databases is that it makes it approximately impossible to generate reports that require joining across databases. Not without writing new and relatively complex application code to do what used to require a simple SQL query to accomplish anyway. Of course if you have the sort of business with scalability problems that require abandoning or restructuring your database on a regular basis, then placing that kind of data in a shared database is probably not such a great idea. It should also be said that common web APIs as a programming technique are much harder to use and implement reliably due to the data marshalling and extra error handling code required than just about any system of queries or stored procedures against a conventional database. The need to page is perverse, for example. That does not mean that sort of tight coupling is appropriate in many cases, but it is (typically) much easier to implement. Web APIs could use standard support for two phase commit and internally paged queries that preserve some semblance of consistency. The problem is that stateless architecture makes that sort of thing virtually impossible. Who knows which rows will disappear when you query for page two because the positions of all of your records have just shifted? Or which parts of a distributed transaction will still be there if anything goes wrong?
winnebago 3y ago
Different business units often want aggregate, filter, join and transform data in different ways - sometimes in ways that are hard to anticipate. In general, the closer to the persistence layer you can perform those transformations, the better they will scale. If you pull the transform into the app layer, you need to move and serialize more data. If you pull the transformation into a constellation of apps, you need to move and serialize a constellation of data. (edit: formatting)

eddd-ddde 3y ago

I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative?

arrowleaf 3y ago
Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?
- eddd-ddde 3y ago
  Because that single application already exists, in my case its postgres. Pgweb is one of my interfaces, admin dashboard for free, and I am sure the changes I make there are as valid as changes through any other interface. I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database... The database is that "single appplication", no need to write custom endpoints for every operation when SQL is good enough. It's all a spectrum of course, today you can even go as far as to use something like pg_graphql and you don't even need to write a REST api yourself. Edit: I forgot to answer the last section, but postgres can totally handle autorization with RLS for instance, allowing users to only see their own data, or maybe data marked as public, etc.
  - DasIch 3y ago
    If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it. It is not realistic that you can trust everyone who needs to access the data with access to the database as they might easily cause problems with poorly written queries. Additionally you may want invariants maintained, that the database cannot maintain but an application in front of it can. Also for historical data or analytical queries postgres is not ideal either, so you probably want to move the data into some OLAP database or datalake. > I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database... If the website and the scraper are just parts of the same application, it makes sense to do this but if they are genuinely different applications. I would use different databases here.
    - Akronymus 3y ago
      > If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it. I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far.
      - JackFr 3y ago
        > I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far. I found such an environment to be simply terrible. In general, I think stored procs certainly have their place, but if ALL access is through stored procs, you better have a schema that’s basically set in stone otherwise dev will turn into a nightmare.
  - jrumbut 3y ago
    > Because that single application already exists, in my case its postgres. This is exactly what postgres was designed for! Tens of thousands of hours of work over decades to solve the problem of relational database management. That's why we call it an RDBMS! Which isn't to say you shouldn't make your own API ever. There are a lot of situations where you don't want things to connect directly to postgres. But you shouldn't be afraid of having multiple systems connect to postgres. It has incredibly mature and robust features to accommodate that use case. It's the expected use case.

butlerm 3y ago

Large ERP systems do that sort of thing as a matter of course and have for decades now. It does require careful planning and design. I mean AR / AP / scheduling / manufacturing / inventory and so on. The main downside of splitting everything into isolated databases is that it makes it approximately impossible to generate reports that require joining across databases. Not without writing new and relatively complex application code to do what used to require a simple SQL query to accomplish anyway. Of course if you have the sort of business with scalability problems that require abandoning or restructuring your database on a regular basis, then placing that kind of data in a shared database is probably not such a great idea. It should also be said that common web APIs as a programming technique are much harder to use and implement reliably due to the data marshalling and extra error handling code required than just about any system of queries or stored procedures against a conventional database. The need to page is perverse, for example. That does not mean that sort of tight coupling is appropriate in many cases, but it is (typically) much easier to implement. Web APIs could use standard support for two phase commit and internally paged queries that preserve some semblance of consistency. The problem is that stateless architecture makes that sort of thing virtually impossible. Who knows which rows will disappear when you query for page two because the positions of all of your records have just shifted? Or which parts of a distributed transaction will still be there if anything goes wrong?

winnebago 3y ago

Different business units often want aggregate, filter, join and transform data in different ways - sometimes in ways that are hard to anticipate. In general, the closer to the persistence layer you can perform those transformations, the better they will scale. If you pull the transform into the app layer, you need to move and serialize more data. If you pull the transformation into a constellation of apps, you need to move and serialize a constellation of data. (edit: formatting)