[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Problem in restablishing relationships

Experts,
I have a table called tblLoans. One of the fields is labeled LoanStatusID and it gets data from a supporting table called tblLoanStatus.

There can be many different types of loan statuses. For example, a loan could be open, pending or in bankruptcy. There can only be one status for a loan at any point in time, however the status of loan can change. For example a loan could be pending and then become open.

Let’s say there is a loan with a Pending status. There are many reasons for a Pending loan.

I considered the following design:
tblLoans
LoanStatusID

tblLoanStatus
LoanStatusID (PK)
PendingLoanID (FK)

tblPending
PendingID (PK)
Pending

tblOpenPendingItems
OpenPendingItemsID (PK)
PendingReason

This is as far as I get and need help going forward. I need to track the LoanStatus so I suspect at least another table and some work on the above. Please keep in mind that Loan Status could be something besides Pending, e.g. Open, Close, Bankruptcy, Repossession, Place for Collections, etc.


0
Frank Freese
Asked:
Frank Freese
  • 5
  • 4
  • 2
  • +1
4 Solutions
 
BusyMamaCommented:
How about something like this, where you keep all of the statuses in one table, with dates and reasons?


tblLoans
LoanID

tblLoanStatus
LoanStatusID (FK)
LoanID (FK)
LoanStatusBeginDate
LoanStatusEndDate
LoanStatusReason

tblLoanStatusLookup (optional - contains the loan status definitions)
LoanStatusID
LoanStatusDescription
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You want to run along the lines of what BusyMama suggested:

1 Table for the loans
1 Table for lookup of the different types of statues involved.
1 Table for recording when a loan status changes.

  I might make the last a bit more generic by doing:

tblAuditTrack
AuditID - PK
AuditDateTime
Table - Table name
RecordKey - PK of record that was changed.
FieldName - Name of field changed
NewValue
ChangedBy

 There are lots of examples floating aorund of that.  I would not record a start/end date as then you get into overlapping time spans between records, which only complicates things.

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
how about adding a date field to your table tblLoanStatus to have a historical information of a loan status.
this would mean that each time an status change of a loan occurs, you will be adding record for that particular loan in tblLoanStatus with the current date.

your table structure will be


tblLoans
LoanID

tblLoanStatus
LoanStatusID (pK)
LoanID (fk)
StatusID (FK)
StatusDate

tblStatus
StatusID
Description


0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
sorry, did not refresh...
0
 
Frank FreeseAuthor Commented:
I have in tblLoans LoanOriginatingDate.

I can see how under tblLoanStatus where we connect to tblStatus. I could always add the StatusDate. But let's say that the loans Status is Pending and the reason for Pending might be lack of proper identification. The user needs to see why the loan was Pendingv for there can be many reason why a loan was pending.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<But let's say that the loans Status is Pending and the reason for Pending might be lack of proper identification. >>

  Add "StatusReason" to tblLoans as a free text field, or you might decide to do another lookup table (which I would suggest).

  You would also track that as a field that might change.

Jim.
0
 
Frank FreeseAuthor Commented:
Jim,
I kind-of-understand the direction your going but somewhat confused on how this record would look?

tblAuditTrack
AuditID - PK
AuditDateTime
Table - Table name
RecordKey - PK of record that was changed.
FieldName - Name of field changed
NewValue
ChangedBy
0
 
BusyMamaCommented:
This is fun ... :)  I like it when there are multiple ideas; although these are kind of similar.

Does the Pending "reason" ever change without the status changing?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Your loan table would look like this:

tblLoans
LoanID - PK
LoadOrigDate - Date/Time
LoanStatusID - FK to tblLoanStatuses
LoanStatusReason - FK to tblLoanStatusReasons

tblLoanStatuses
LoanStatusID - PK
LoanStatusDesc - Text

1   Open
2   Pending
3   Closed
4   Bankruptcy
5   Repossession
6   Placed for Collections

tblLoanStatusReasons
LoanStatusReasonsID - PK
ReasonDesc - Text

1  Credit check
2  Income verification
3  Incorrect facts on loan application
4  Missed Payments
5  Abandoned property

  I suggest a lookup for the reason that someone may ask for a given status why that status was assigned.  For example on a Repossession, it may be due to missed payments or abandonment.  If you leave the reason free text in tblLoans, someone might do:

Abandonement.
Abd.
They left.

  Trying to report off that would be impossible.  However if a lookup, you may also have to address they fact that not every reason can be applied to every type of status.  

  Now for the change.  New loan is created.  So change history reads:

AuditID / Time           table        PK   field              New value   changed by
1   09/19/11 12:01    tblLoan    1   LoanStatus    1                  134

Now the loan process moves forward, for a credit check first and then an income check, so the audit table now looks like this:

AuditID / Time           table        PK   field                New value   changed by
1   09/19/11 12:01    tblLoan    1   LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1   LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1   StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1   StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1   LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1   StatusReason  3                  755

  Can you follow through what happened?

  That's one way of structuring it.  There are a number of ways to do it.  For example, if you wanted to audit just this one table, then instead of one field per audit record, you might decide to just save a copy of the entire loan record in the audit table as one record.

  You might also keep a "version number" in the loan table so audits could be grouped:

AuditID / Time           table        PK   Version   field                New value   changed by
1   09/19/11 12:01    tblLoan    1     1            LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1     1            LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1     2            StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1     3            StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1     4            LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1     4            StatusReason  3                  755

  incrementing it each time the loan record as saved.  

   There are no right or wrong answers here in terms of design, just what will work best for you and the problem at hand.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Oops, messed that last up slightly:

  You might also keep a "version number" in the loan table so audits could be grouped:

AuditID / Time           table        PK   Version   field                New value   changed by
1   09/19/11 12:01    tblLoan    1     1            LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1     2            LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1     2            StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1     3            StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1     4            LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1     4            StatusReason  3                  755
0
 
Frank FreeseAuthor Commented:
folks,
since we are having soooooo much "fun" I need a little time to study this.
0
 
Frank FreeseAuthor Commented:
by the way, I use lookup tables as much as possible. I've been tasked with developing a new db from off of one that was macro driven, no code, no keyed fields, no business rules, no relationships, few fields not labed 1,2,3,4, adding values then storing them in tables, etc  so you can imagine the garbage in and out being experienced. Ever try adding text? They tried. Yes, a pending reason can change without Pending itself changing.
0
 
Frank FreeseAuthor Commented:
thanks folks - wish I had 1,000 pts to give. I truly appreciate everyones input and can take this forward now.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now