-1

Here's what a typical application that adopted the client-server architecture looks like:

  1. A client-based frontend issues requests to a company backend server
  2. The company backend server accepts the request and hits a company DB
  3. A company DB fetches or manipulates the data (if necessary) and responds back to the backend server
  4. The server responds back to the frontend client that triggered the whole chain of events

But what if clients have their own DBs?

We have what seems to be a pretty unusual way of handing that. The key peculiarity is we don't have a separate backend layer

  1. A frontend client, a medical desktop app, issues a request to a DB with what we call an "app SQL" string. The DB-hosting machine would be typically stored in a server room in the same facility.
  2. The DB responds back with an SQL that matches the received "app SQL". There's a DB table for "app SQLs" with columns for names and actual SQL statements. Those statements may be simple SELECTs or procedure calls. Say, the frontend would call SELECT * FROM APP_SQL WHERE NAME = 'GET_ACTIVE_SERVICES' and the DB would respond with a "DTO" whose text field would store this string: SELECT * FROM SERVICE WHERE active_status = 1. "App SQL" strings, e.g. GET_ACTIVE_SERVICES, are roughly similar to endpoints, that is they provide a layer of abstraction. A DB may wish to return a different SQL at some point, and the frontend wouldn't know.
  3. The frontend, upon receiving the SQL, uses it to hit the DB again to actually get or modify the data it wants.
  4. The DB processes the request (fetches or modifies its data), responds back.

Sometimes, a frontend app skips the "app SQL" phase and hits the DB with an actual DQL/DML statement right away. Typically, it happens when it only needs to perform a trivial SELECT/UPDATE/DELETE

It seems wrong. The DB effectively doubles as a backend server. You should see its procedures: complex walls of imperative logic with ifs, cases, and loops. SQL was never designed for that. It was designed to efficiently (and declaratively) fetch and manipulate data, and it's what it should be used for

But how are modern apps with client-based DBs designed instead?

In particular, where should the backend be? If it starts up along with the frontend application on the same machine, then it would likely increase the start-up time (what with the web server bootstrap, etc.). Then, would we have to set up a backend server on either a third machine or the same machine as the DB (so it's running non-stop alongside the DB)?

Improve this question
5
  • 4
    What benefit is this "app SQL" indirection supposed to bring? This sounds like some part of a deployment/update process is painful, so someone has found they can bypass this friction by storing SQL fragments in a table (as opposed to, say, using stored procedures or maintaining stable interfaces via views). The main purpose of backend servers is a security barrier (control who can run which queries), but this "app SQL" hack provides none of that because the end user's device remains responsible for issuing the actual queries directly to the DB. Commented Jul 28, 2024 at 16:42
  • @amon I guess the deemed benefit was to somewhat decouple the app from the DB. But don't expect me to advocate for our design. I believe I made it clear in my question that I don't consider our design the right one and seek opinions on what it's supposed to be instead Commented Jul 28, 2024 at 17:08
  • 1
    @SergeyZolotarev, generally speaking, problems with "coupling" relate either to a disorderly coupling between application layers - and where the remedy is a more orderly coupling, certainly not decoupling - or it relates to a completely different notion of "vendor lock-in" where the remedy is ensuring that the application is not unreasonably reliant on an expensive third-party technology whose rent must be periodically renegotiated with the vendor. Commented Jul 28, 2024 at 17:23
  • Is there a specific need to have a fat client? For example, does your client need to access local hardware resources? This sort of requirement will greatly affect the answer to your question. Ideally you'd have no client at all (other than a browser) to make it easier for the IT department to support. Commented Jul 29, 2024 at 21:03
  • @JohnWu the facilities' hardware must be stored on the premises, if that what you're asking Commented Jul 30, 2024 at 2:57

4 Answers 4

Reset to default
1

But how are modern apps with client-based DBs designed instead?

Modern apps are designed with a "backend" layer which wraps the database calls and applies logic and security etc.

In particular, where should the backend be?

In the same place as the database. So in your example of a doctors office where they have a server with the db on in a back room. Deploy the backend to that server.

If the client has a datacenter with the db in, put the backend there.

If the app runs on a phone or single computer, put the backend there

Improve this answer
2
  • Thank you. Do they typically deploy the backend on a separate machine than the DB (so a server room would have a backend machine and a DB machine)? Commented Jul 29, 2024 at 10:21
  • I think that's an implementation detail tbh. Do you want to sell the dr's office a new computer? prob not, is it better to spin up a new instance in your cloud datacenter for the BE? prob Commented Jul 29, 2024 at 10:41
8

The premise of this question seems to imply that some standard application architecture exists that the rest of us know about, but the original designers of this system ignored with wanton disregard for the consequences. It's been my experience over the years that oddball designs are made for a handful of reasons:

  • The original developers didn't know better. A more suitable solution existed (like stored procedures), which was not used due to a lack of experience or understanding.
  • The system was built so long ago that the "go-to solution" we think about now had not been invented yet.
    • And when the technology was modernized, it wasn't worth the effort to rewrite battle-tested code.
  • The original developers were asked to implement some very difficult requirements and this "app SQL" was a carefully designed solution that made some hard choices about the benefits and drawbacks of whichever technologies were available at the time (see point #2 above).

Thinking in terms of "normal" application architecture might not be very useful here. Presumably, years have passed and technology is quite different now. Had this started today as a greenfield project, the engineering team would very likely have made different decisions.

The challenge here is crawling into the heads of the original designers of this system. Unfortunately, there is no general way to do this. The first thing I would do is analyze the "app SQL" for all the customers that use this software, and understand the deployment process. Deploying to on-premise hardware, especially if the people who own that hardware have little IT experience, can present challenges that we just don't need to deal with when professional developers and devops engineers maintain the ecosystem.

I wouldn't be surprised if there were minor differences in the SQL between customers. I also wouldn't be surprised if there was some way for the customer to change the SQL — or to discover that there used to be.

Only after discovering why this design was chosen would I entertain a different approach for future application changes. Even after considerable research, this information might be lost to the sands of time. If that is the case, I would feel comfortable suggesting a different approach for future application changes provided the new design comes with more benefits than drawbacks, and not just because some stranger on the Internet said "best practice".

Improve this answer
4
  • 2
    Since this is a medical app, one place worth looking under the third point is what is the scope of release validation. Are the SQL queries in the DB considered part of your release validation process, or is this a mechanism to enable logic changes in the field to bypass that by classing them as 'configuration' Commented Jul 28, 2024 at 18:18
  • If this is medical software, you also need to consider foreseeable error/risk. So classifying the SQL queries as "configuration" might not get you out of trouble. There likely needs to be some form of process around making any changes to this SQL, since errors could have catastrophic consequences. Commented Jul 29, 2024 at 8:46
  • @JonasH ideally yes. In practice, configuration is often significantly lighter weight approval since it only needs to work for one site, and if done by the site themselves often hits loopholes in regulation. Commented Jul 29, 2024 at 9:46
  • For example, there are no FDA regs around end user changes made to config in violation of documentation. Commented Jul 29, 2024 at 9:47
4

It sounds to me that original designers tried to make the Application "generic" - so that it would work with any backend database - by adding a "translation" layer:

  • The App asks the "App SQL" store how to get, say, "Client_Details".
  • The store responds with a line of, say, Oracle-specific SQL that goes to a particular table in a particular schema.

Their thinking (probably) was that they could "port" the application to a.n.other DBMS by simply "translating" everything in the "App SQL" store. The new store could return, say, PostgreSQL equivalents for each and every SQL statement.

It probably sounded like a Great Idea(TM) when they built it but, practically, it's not.

All DBMSes are proprietary and can choose to do things [well/efficiently] in subtlety different ways. If the Application only supports one [generic] way of doing things, you'll likely see the Lowest Common Denominator in terms of Performance and there's very little you can do to improve things. Yes, you can change the individual SQL statements, you can't optimise the way in which they are used without changing the Application code, thereby making it DBMS-specific, which goes again the whole "generic" idea.

Common alternatives to this, as you say, "unusual" implementation are:

  • Stored Procedures, given that the call and return mechanisms for these are pretty standard across DBMSes, or
  • Data Access Classes, where each subclass provides the different flavours of SQL.

The client would specify their DBMS and you'd ship them the relevant, DBMS-specific, Data Access classes.

Improve this answer
2

The design you're describing seems confusing, as is the terminology you use. And the purpose of the design is unclear.

All applications essentially have three layers: storage, algorithms, and screens.

It's possible for all of these to exist on a single standalone computer (rather than the storage being centralised, and the screens accessed in a distributed way), and its normal nowadays to use an SQL engine for at least the storage (if not a significant part of the algorithms as well).

Usually "front end" and "back end" are synonyms for client and server, in the context of an application which uses network connections to access centralised storage from multiple clients.

The "database" is, stereotypically, the facility which provides the centralised storage, although the word has acquired wider, sloppier usage.

The importance of the front/back distinction blurs a little if everything is on the same machine. Then, I suppose, the front end will be whatever context the screens are in (typically the context of a general-purpose programming language with some kind of graphical UI), the back end will be whatever context the storage is in (typically an SQL engine), and the algorithms go wherever.

It's not uncommon for standalone applications to be written this way to leverage familiar tools, patterns, and skills - especially, the tools, patterns, and skills which relate to the use of an SQL engine.

However there have been technologies in the past like MS Access which can integrate all three locally into one development environment and one deployment package (as well as also still being able to cope with client-server use), so it's by no means necessary for the front/back distinction to exist when there is no true client/server distinction.

And there's nothing to stop a fully bespoke application, with no SQL engine, from doing everything locally for itself.

So in light of all that, what do you mean by:

how are modern apps with client-based DBs designed instead?

The most straightforward answer is that standalone applications nowadays are usually designed broadly the same as client-server applications, even when the absence of the client-server aspect means certain architectural features would not be necessary.

Improve this answer
5
  • What I meant is, where should the backend be? If it starts up along with the frontend application on the same machine, then it would likely increase the start-up time (what with the web server bootstrap, etc.). Then, would we have to set up the backend server on either the third machine or the same machine as the DB (so it's running non-stop alongside the DB)? Commented Jul 29, 2024 at 3:13
  • And yes, I am absolutely certain that a separate backend layer is necessary. Writing backend logic in SQL is insane. SQL should only be used to declaratively fetch and manipulate data, period Commented Jul 29, 2024 at 3:14
  • @SergeyZolotarev, the "backend" is the DB (or the whole server-side infrastructure, if it's more complicated than just a DB), so I'm not sure what you mean when you imply the "backend" might be separate from the DB. The (durable) storage is always the essential member of the "backend" of an application. Commented Jul 29, 2024 at 8:23
  • It's the layer that you referred to as "algorithms" in your answer Commented Jul 29, 2024 at 8:34
  • @SergeyZolotarev, the algorithms layer is not, alone, the backend. If considered separate then it's the middle layer in this three-layer conception. It's also not unusual for some algorithms to be executed in the backend and some in the front, and often algorithms which are executed in the backend can be stored in the front (as with SQL queries that are submitted as text from the client side, rather than captured as stored procedures on the server side). Commented Jul 29, 2024 at 12:41

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.