G
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
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