DB + Web forms question

WEB TMS WEB Core
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