Solved

Problem in restablishing relationships

Posted on 2011-09-19
13
328 Views
Last Modified: 2012-05-12
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
Comment
Question by:Frank Freese
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Assisted Solution

by:BusyMama
BusyMama earned 25 total points
ID: 36562316
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
 
LVL 57
ID: 36562378
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 25 total points
ID: 36562407
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36562415
sorry, did not refresh...
0
 

Author Comment

by:Frank Freese
ID: 36562522
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
 
LVL 57
ID: 36562550
<<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
 

Author Comment

by:Frank Freese
ID: 36562664
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
 
LVL 7

Expert Comment

by:BusyMama
ID: 36562847
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
ID: 36562901
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
ID: 36562934
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
 

Author Comment

by:Frank Freese
ID: 36563330
folks,
since we are having soooooo much "fun" I need a little time to study this.
0
 

Author Comment

by:Frank Freese
ID: 36563374
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
 

Author Closing Comment

by:Frank Freese
ID: 36563456
thanks folks - wish I had 1,000 pts to give. I truly appreciate everyones input and can take this forward now.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question