I've hit a confusing subject and I'm wondering if someone can help me out here.
Background:
I have a [Jobs] and [PayApplications] table. A "Job" has many [PayApplications]. Pay applications are submitted on a monthly basis for each job. A job might last up to two years so have couple dozen pay applications. A job might not have a pay application on some months. I started out designing this db with the [PayApplications] table having an [AppNumber] field that worked to sort the pay applications. Each "PayApplication" would store currency values for work completed that month, and that's what we bill for. (Actually it's a collection of a lot dollar amounts, I'm simplifying) So for example billing in January could be $50k, February $30k, March $60k, total billing to date $140k.
For my purposes a report on A [PayApplication] needs to display summations for ALL previous pay applications. (See standard AIA Form G703 for an example report that I will need to be generating, maybe this will clarify some things)
Given the data model as described my queries were having to collect and summarize a relatively large amount of data. So I got to thinking, for each [PayApplication] would it be better to store work completed TO DATE? So given the example above we would have January $50k, Feb $80k, March $140k. This way when I need to display the summations of ALL previous [PayApplications] I only have to look up the ONE previous pay application instead of having to do summation queries on ALL the previous pay applications. Work completed this month is simply = (completed to date this app) - (completed to date previous app)
The Problem:
I thought that a recursive relationship between pay apps would be appropriate here instead of having an [AppNumber] field. I'm having trouble deciding on how to most appropriately apply the relationships and integrity. So each PayApp succeeds another PayApp UNLESS it's the first PayApp for the job. On the other hand each PayApp precedes another UNLESS it's the latest pay app for a job. Either would be a one-to-one relationship.
For the recursive relationship I would need a PreviousPayAppID OR NextPayAppID field. These fields would need to be indexed w/ NO duplicates EXCEPT I need to somehow allow a null value for the first and latest PayApps??? But to allow for null it seems I need to set these to indexed w/ duplicates allowed. This opens up the possibility of PayApps having multiple previous or following apps. To enforce any kind of data integrity my fields can't allow nulls as far as I can tell.
Questions:
- Am I over complicating everything and should I forget about recursion for this?
- Should my [PayApps] table maintain a PreviousPayAppID field, a NextPayAppID field, or both? I figure Data Macros will come in handy here.
- If the answer to #2 is not both, how do I query the first or latest PayApp in the series?
Here is a simplified model: