Form/Query/Table Design

[Guy Horton]

Scenario

A leasing company raises a lease agreement for distribution to the customer, when the customer decides they want to proceed with a lease. The lease agreement is for a set term (ie. 18, 24, 36, 48, x months...) describes the assets to be leased and the monthly lease payments, which may vary over the term of the lease (ie. month period 1 to 6 - $50, 7 to 12 - $35, 13 to 18 - $20) The following brief snapshot of the database schema describes the proposed physical design:

tblLease (1:M relationship with tblLeaseAsset)
LeaseId PK
Term

tblLeaseAsset (1:M relationship with tblLeaseAssetPayment)
LeaseAssetId PK
LeaseId FK
Asset Description

tblLeaseAssetPayment (Intersection table)
LeaseAssetId PK/FK
LeaseTermId PK/FK
Lease Payment

tblLeasePaymentPeriod (1:M relationship with tblLeaseAssetPayment)
LeaseTermId PK
LeaseId FK
FromPeriod
ToPeriod

Form Design

Generally, I would implement the Add/Edit/Delete Lease Agreement form in single form view to manage the Lease and use either continuous or datasheet view sub forms to manage the maintenance of Lease Assets, Lease Asset Payment Periods, Lease Assets and Lease Asset Payments.

However, in this instance I would like to combine the Lease Asset and Lease Asset Payment data entry screen as a combined subform, if possible, with a variable number of Lease Asset Payment columns - 1 for each Lease Payment Period

In a sense this requires a pivot table type view where each tuplet in tblLeasePaymentPeriod becomes a column in the form and each tuplet in tblLeaseAsset is a row combined with tblLeaseAssetPayment.

ie.
Period Period Period
Asset Id Asset Description 1 to 6 7 to 12 13 to x ...

12345 A Car $50.00 $35.00 $20.00
67890 A Boat $100.00 $60.00 $50.00

Unfortunately Pivot style queries aren't usually updateable. I have also considered a subform within a subform, instead of triggers, and using temporary tables, or alternatively the database could be denormalised by moving the Lease Payment field into tblLeaseAsset as LeasePayment1, LeasePayment2, LeasePayment3 providing the business is happy to restrict the number of variable lease payments allowed.

Has anyone else got any ideas or solved a similar problem?

such as an improved database design, to view/stored procedure (T-SQL) and form design.

Your assistance appreciated,

Best Regards
Guy

 

[Ron Weiner]

I have had to do this kind of thing before. Basically what I did was to create two temp tables that I would populate as the form opened. One table held the Demoralized Pivoted Data and the other contained MetaData instructions that described how to display the data. It described whether to use a TextBox or Combo, what colum to bind the data to, what the controls label text should be, what the min - max values ought to be, the combo's Row Source, the order the columns were to appear, etc.

Then I used a semi smart subform (DataSheet) that could morph itself into the shape described by the MetaData table, and bound each control to the appropriate column in the datatable. The form held about 50 each combo and textboxes whose properties I set as the form is opening.. I guessed that the absolute max potential number of columns that the client would EVER use would be 24, and then doubled my guess just in case I was just being stupid. It turned out that in normal use my customer rarely uses more than a dozen columns so I am safe.

At this point the user is dancing around your form making changes to the data in the temp data table.

The real bitch was re-pivoting the changed data back into the normalized database structure when the form was closed. This required lots of looping code updating or adding rows to the normalized data structure.

Now that does sound like a lot of steps and a fair amount of work, but it has been working with out problem in an multi-user enterprise class application with a Sql 2K backend for nearly two years.

I hear you grumbling about the performance hit and how slow something like this is. All I can say is while it could take a couple seconds for this form to display itself in a situation where the server was busy, but the customer has not complained, and the ability to see and EDIT their data in this format is more than worth the wait.

Since then we have Webized the whole shebang using IIS and ASP without making a single change to the data structure or the methodology. Today most of the users are using the web based interface, but there are a few diehards that like using Access and I think the performance is a little better in access than IIS. Ease of deployment is another issue.

If you are hosting the backend in Sql Have a look here http://www.rac4sql.net/ for a truly great tool for rotating data. We would not have been able to do what we are doing without this tool. It is cheap, easy to use, and can shape your data in almost unlimited ways.

--
Ron W
www.WorksRite.com
Scenario

A leasing company raises a lease agreement for distribution to the customer, when the customer decides they want to proceed with a lease. The lease agreement is for a set term (ie. 18, 24, 36, 48, x months...) describes the assets to be leased and the monthly lease payments, which may vary over the term of the lease (ie. month period 1 to 6 - $50, 7 to 12 - $35, 13 to 18 - $20) The following brief snapshot of the database schema describes the proposed physical design:

tblLease (1:M relationship with tblLeaseAsset)
LeaseId PK
Term

tblLeaseAsset (1:M relationship with tblLeaseAssetPayment)
LeaseAssetId PK
LeaseId FK
Asset Description

tblLeaseAssetPayment (Intersection table)
LeaseAssetId PK/FK
LeaseTermId PK/FK
Lease Payment

tblLeasePaymentPeriod (1:M relationship with tblLeaseAssetPayment)
LeaseTermId PK
LeaseId FK
FromPeriod
ToPeriod

Form Design

Generally, I would implement the Add/Edit/Delete Lease Agreement form in single form view to manage the Lease and use either continuous or datasheet view sub forms to manage the maintenance of Lease Assets, Lease Asset Payment Periods, Lease Assets and Lease Asset Payments.

However, in this instance I would like to combine the Lease Asset and Lease Asset Payment data entry screen as a combined subform, if possible, with a variable number of Lease Asset Payment columns - 1 for each Lease Payment Period

In a sense this requires a pivot table type view where each tuplet in tblLeasePaymentPeriod becomes a column in the form and each tuplet in tblLeaseAsset is a row combined with tblLeaseAssetPayment.

ie.
Period Period Period
Asset Id Asset Description 1 to 6 7 to 12 13 to x ...

12345 A Car $50.00 $35.00 $20.00
67890 A Boat $100.00 $60.00 $50.00

Unfortunately Pivot style queries aren't usually updateable. I have also considered a subform within a subform, instead of triggers, and using temporary tables, or alternatively the database could be denormalised by moving the Lease Payment field into tblLeaseAsset as LeasePayment1, LeasePayment2, LeasePayment3 providing the business is happy to restrict the number of variable lease payments allowed.

Has anyone else got any ideas or solved a similar problem?

such as an improved database design, to view/stored procedure (T-SQL) and form design.

Your assistance appreciated,

Best Regards
Guy