General database & bound/unbound controls question

[jtwatssi]

This post is going to be quite long and I apologize for the long read, but I have some issues that I have some questions on and would like to get some input from other users. This is not so much technical coding questions, so much as it is general end user usabilty and flow.

The main point of my post is...should I use bound controls on the data entry/edit forms of my app or should I use unbound controls and copy the data manually from the controls to the dataset in response to say...an OK button OnClick event?

The advantage to using bound controls is obvious (especially in the case of using a bound grid control for the detail dataset in a master/detail relationship) in that much less coding is required.

However, I see some caveats to using bound controls.

As a simple example, lets say that I create a couple of tables for customer invoices. In the real world it would of course be a bit more complex than this, but this should illustrate my point.

I have the master table which hold the invoice info (customer, invoice number, etc.) and a detail table which holds the invoice line items (part no., description, price, unit of measure, etc.) in a grid control.

I quite often use an auto-increment field as the primary key of the master table and then use an INTEGER field as the foreign key in the detail table and set the link up as usual, with maybe a smallint field to number the line items, with the combination of the foreign key field and the line item field constituting the compound primary key of the detail table.

I also like to set up referential integrity between the auto-increment field in the master table and the foreign key INTEGER field in the detail table so as to prevent any possibility of orphaned records in the detail dataset. Of course you could enforce this in code, but it makes sense to have the MySQL server enforce this (thats what referential integrity is for!) since it would never make any sense to allow an invoice line item to exist that does not reference an invoice master record.

Now my user clicks on the "New Invoice" button in the application and up pops the invoice editing form with controls at the top of the form (text edits, checkboxes, etc.) linked to the MASTER table and a grid at the bottom of the form linked to the line items DETAIL table. Before the form displays, the MASTER table's Insert method is executed so all controls are empty, waiting for data input.

Now lets say my end user begins typing in the grid first. Remember, end users aren't necessarily thinking about the "order" in which they are to enter data, they just want to fill out the form and click the "OK" or "Save" button or whatever when they are finished and the data is saved.

The problem is, as soon as the user tries to enter a second record in the grid, the grid tries to post the data but an exception is raised because the MASTER table has not been posted yet, hence the auto-increment field does not have a value yet, and therefore the foreign key of the detail dataset does not have a value yet either.

A solution to this problem is to post the MASTER dataset record in the BeforePost event of the detail dataset but then, if there are fields in the master dataset that require a value and the user has not yet entered them, or there's some other type of data constraint, then another exception(s) is raised! Also, using cached updates doesn't help either because no auto-increment numbers are generated until Commit is executed (I think that's the case).

The best solution that I can see is to not use bound controls at all but instead use unbound controls and write all edits to the dataset(s) in the correct order in one try...except block contained in the OnClick event of "OK" or "Save" button and then raise any exceptions within the try...except block if user has omitted any required data or has any other data integrity violations.

Does anyone have any input on this?

Thanks.

JTW

[jtwatssi]

I think I came up with a solution to my own question.

The thing to do is use cached updates so that all edits are applied at once by putting the CommitUpdates command in the onClick event of the OK button (inside of a try...except block). And, instead of using an auto-increment field, retrieve the next available Integer key from a table manually so that it can be assigned in the OnNewRecord event for both the master dataset and the detail dataset.

That way you don't have to wait for CommitUpdates to get the auto-increment value and any data integrity violations can be caught in the try...except block where the CommitUpdates statement is.

Does that make sense?

[bork]

Hello

Your method is applicable in the single-user applications. But it can give troubles in multi-user applications when a lot of users attempt to get new Integer key from a table manually at the same time.

[jtwatssi]

Well this is definately a multi-user app, so what would you recommend?

[jtwatssi]

It seems to me that if you retreive the next available integer in code within a transaction that there would be no more of a multi-user conflict than any other read or write to the server. For instance, just prior to showing the form you could write:

Code: Select all

function GetNextKeyValue: Integer;
begin
  try
    with tblKeys.Connection do
      if not InTransaction then
        StartTransaction;
    try
      tblKeys.Open;
      Result := tblKeys.FieldByName('NextInvoiceKey').AsInteger;
      tblKeys.Edit;
      tblKeys.FieldByName('NextInvoiceKey').AsInteger := Result + 1;
      tblKeys.Post;
      with tblKeys.Connection do
        if InTransaction then
          Commit;
    except
      on E: Exception do
      begin
        tblKeys.Cancel;
        with tblKeys.Connection do
          if InTransaction then
            Rollback;
        ShowMessage(E.Message);
      end;
    end;
  finally
    tblKeys.Close;
  end;
end;

By using a transaction you guarantee that the user retrieves a value and increments the field.

Would it still be better to go back to my original solution of manually putting all updates, in the correct order, inside a try...except block contained in the OK button OnClick event?

[bork]

In a multi-user application two users can try to get new key value at the same time. In this case the following situation occurs:
1-st user: start transaction
2-nd user: start transaction
1-st user: get the last key value from the table NextInvoiceKey (for example 10)
2-nd user: get the last key value from the table NextInvoiceKey (for example 10)
1-st user: increase last key value and save to the table NextInvoiceKey (new value 11)
2-nd user: increase last key value and save to the table NextInvoiceKey (new value 11)
1-st user: commit transaction - all OK
2-nd user: commit transaction - ???
On commit transaction by 2-nd user we can get two results:
- the table NextInvoiceKey doesn't have a unique constraint: after committing second transaction table will contain two equal key values and 1-st and 2-nd users will try to insert two records with the same key values
- the table NextInvoiceKey has a unique constrain: 2-nd user gets an error on committing transaction

So I don't recommend this method.

[jtwatssi]

Okay, I see what you are saying. Yes, there would have to be a unique index on the field and you would have to deal with the possible error in code.

So, are you saying the the most reliable way to guarantee a unique primary key in a potentially high traffic environment is to always use an auto-incrementing field for the primary key?

[bork]

The problem is that I don't know the purposes for your application development and I don't know the requirements it faces. So I can not advise you the best solution. I've just informed you about difficulties and problems you may face if you try to generate keys for tables manually. The final decision is definitely yours to make, so you should consider all pros and cons of every way and choose the way that fits your needs best. And remember that there is no universal way that would be the best one in any case. You should choose the way that would fit your application the best.