DB + Web forms question

WEB TMS WEB Core
1

This is somewhat of an age-old debate, but I'm wondering if there's anything new about using WEB Core to build an app with separate forms that let you edit DB fields?

If you're using a DBMS with a REST API, like StellarDS, in most ways this looks like a VCL app using SQL Server or MySQL on the LAN. Delphi's TDataSets and DB-aware controls hide most of the complexities, but the forms are separate things. So I'm curious if anything changes depending on where the DB Connection and/or the DataSet controls are located -- in a DM, the Main form, or the form doing the updates? I've seen them all used.

But also, I'm wondering if there's any reason to favor one of these options or the other:

  • Connection on main form or DM, select a record, send the DATA to a form for viewing and/or editing using normal controls, then get it back and update the DB if it was changed; versus,

  • Select the current record that you want to use, then open the form to view and/or edit the record, and have it interact with the DB itself using DB-aware controls.

If you don't have uniform access rights for everybody, does this impact one of these approaches over the other?

(I've worked on Delphi apps that use both methods in existing code, but I've never really built one from scratch nor had to dive into all of the issues. My apps have mainly used simpler data stores, like INI files -- which aren't very compatible with web apps (since they can't be stored locally) unless you have a way to host them behind an API.)

2

We have modeled the use of DataModules in a TMS WEB Core application similar as how you would use DataModules in a VCL application.

3

I've seen all of these variations used. I'm asking if there are any reasons to prefer one approach over others.

4

Both approaches have their pros and cons, and the choice depends on maintainability, security, and how user access rights are managed. Let's break down the considerations:

Approach 1: Connection on Main Form or DataModule

  • Workflow:
    1. A record is selected.
    2. Data is retrieved and passed manually to another form (non-DB-aware controls).
    3. The form allows users to edit the data.
    4. On save, changes are sent back and applied to the database.

Pros:

:white_check_mark: More Control Over Data Handling

  • You explicitly manage when and how data is written back, reducing unintended updates.
  • Allows business logic validation before updating the DB.

:white_check_mark: Security & Access Control

  • Since editing occurs in a detached dataset (e.g., using local variables or objects), users without write access can view data without modifying it.
  • You can validate user permissions before sending updates to the database.

:white_check_mark: Better Concurrency Handling

  • Prevents locking records unnecessarily (since changes happen locally and are only written back when the user confirms).

:white_check_mark: Easier to Work with APIs or Multiple DBs

  • This model is similar to handling REST APIs or multi-database architectures, making it more flexible.

Cons:

:x: More Code & Complexity

  • Requires additional code for transferring data between forms.
  • Need to manually track changes and update the record accordingly.

:x: Can Be Less Responsive

  • If the dataset is large, pulling all the necessary data first before opening the form can slow down performance.

Approach 2: DB-Aware Controls & Direct Interaction with the DB

  • Workflow:
    1. A record is selected.
    2. A form opens with DB-aware controls (e.g., TDBEdit, TDBGrid), which are directly connected to the dataset.
    3. Any changes are instantly reflected in the dataset.
    4. The form can commit or cancel updates directly.

Pros:

:white_check_mark: Faster Development & Simpler Code

  • Less code is needed because DB-aware controls automatically reflect and update data.

:white_check_mark: Live Data Editing

  • No need to manually pass data between forms; changes are instantly applied.

:white_check_mark: Ideal for Small to Medium Applications

  • If users are always expected to have write permissions and concurrency issues are minimal, this method is straightforward.

Cons:

:x: Potential Security Issues

  • If a user has edit access, they can modify data without additional validation, unless explicitly checked before saving.
  • Access control must be enforced at the dataset or DB level.

:x: Concurrency & Locking Issues

  • If multiple users edit the same record, who wins?
  • DB-aware controls might keep a record "locked" while editing, leading to blocking issues.

:x: Harder to Manage Access Restrictions

  • If different users have different permissions, you need additional logic to disable controls dynamically.

Access Rights & Choosing the Right Approach

If users have different access rights, Approach 1 is the better choice:

  • It allows granular control over who can edit data.
  • Read-only users can view data without affecting the database.
  • Write access can be validated before committing changes.

If all users have uniform access rights, Approach 2 is faster and easier to implement.

Which One to Choose?

Criteria Approach 1 (Manual Transfer) Approach 2 (DB-Aware Controls)
Security & Access Control :white_check_mark: Better for role-based permissions :x: Harder to manage
Concurrency Handling :white_check_mark: Safer, avoids unnecessary locks :x: May cause record locking
Performance :x: Slightly slower (manual data handling) :white_check_mark: Faster (direct DB updates)
Complexity :x: More code required :white_check_mark: Simpler, automatic DB binding
Scalability :white_check_mark: Works well with APIs, multi-DB :x: Limited to direct DB access
User Experience :white_check_mark: More predictable (controlled saves) :x: Risky if the user accidentally modifies data

Final Recommendation

  • If you need strict control over access rights and concurrency, go with Approach 1 (Manual Transfer).
  • If you want rapid development and simplicity, Approach 2 (DB-Aware Controls) is fine for small-scale apps with uniform user access.
2 Likes
5

Wow, Bruno, this is a great synopsis of the issues. Thanks! This should be posted somewhere more accessible. It's the kind of detail that usually comes with lots of experience, and I've seen situations where these guidelines were not followed and they resulted in overly-complex code.

But a more common case that I've observed a lot is a combination where the DB info is passed to the form, either explicitly or implicitly; the form issues a query to get the data (if needed); user edits the form; then the data is saved back to the db (or added if new) before the form exits.

There are lots of ways to slice-and-dice this, but it looks like it mainly boils down to Security & Access Control needs, which is not something I've ever really considered. Usually it's approached from either "copy the data in and out of the form yourself" or "let the DB handle the data movements".

I've seen situations where every form had a TClientDataSet on it that was there only to support DB-aware controls on the form to reduce the need to move data around.

(Did you use ChatGPT to get this? It's unusually well-formatted.)