Normalizing one giant table -- Help

[iuianj07]

Hello guys,

Inspired by dhookom that I should normalize this table properly (which I really should have done anyways, but not 100% confident if I could normalize properly) I attempted to have this table normalized:

Current Table Job_Tracking

The ones that are colored Red are fields that came from our client, they send us a spreadsheet with these fields and we import it into our database (Job_Tracking table)

Job_Tracking table

InvestorNumber
LoanNumber
PropertyNumber
ProspectusID
SecuritizationDate
FirstPeriodDue
PropertyLoanStatus
WatchlistedLoan_YN
Top20
LoanBalance
FS_WFB_Received -- 'FS=Financial Statement_Client Received Date (Received from borrowers not us)
FS_VendorReceived -- 'We are the Vendor
FS_ImageToVendor
RR_WFB_Received -- 'RR=Rent Roll
RR_Vendor_Received
RR_ImageToVendor
OSAR_ImageToVendor
YE2009_YE_PWC_Completed_Date
CollectionComments
FSRR_Required
FSRR_Required_Override
Subserviced
SubservicerName
PropertyName
PropertyStreet
PropertyCity
PropertyState
PropertyZipCode
PropertyTypeCMSA -- 'either Retail, Multi-Family, Office etc.
BorrowerName
NSF
NumberOfUnitsBed
PropertyCount
UW -- 'UW=Underwriting
OSARPropertyNumber
AssumptionDate
East-West -- 'If this Tracking Log came from the East or West office

JobTrackingID
ReportingPeriod --'ex. 2009YE,2010Q1
EntryAnalyst
EntryAnalystCompleteDate
EntryAnalystComments
Analyst
AnalystCompleteDate
AnalystComments
QC -- 'QC=Quality Control=Reviewer
ReturnForCorrectionDate --'If QCer found mistakes made by Analyst
QCComments
ResubmitforQCDate --'Date when Analyst sent back the work that had mistakes
QCCompleteDate
OnHoldForIssue -- 'Puts a Loan On Hold if can't compelete
ResolvedIssueDate
SignedOffBy --'final QCer
SignedOffDate
ConsolidatedStatement
InvoiceNumber --'When we bill the client for what we completed, I assume you will say that this should be in another table?
InvoiceDate
FinancialStmt --'hyperlink data type to open documents
RentRoll --'hyperlink data type to open documents
24HourRush --'Yes/No Type
1-3DayRush --'Yes/No Type
TurnaroundDate


Now I tried to create new tables (on the drawing board) to try to properly normalize, below are my tables.

Job_Tracking Table
JobTrackingID
IssuesID
InvoiceID
LoanStatusID
InvestorNumber
LoanNumber
PropertyNumber
ProspectusID
SecuritizationDate
FirstPeriodDue
PropertyLoanStatus
WatchlistedLoan_YN
Top20
LoanBalance
FS_WFB_Received -- 'FS=Financial Statement_Client Received Date (Received from borrowers not us)
FS_VendorReceived -- 'We are the Vendor
FS_ImageToVendor
RR_WFB_Received -- 'RR=Rent Roll
RR_Vendor_Received
RR_ImageToVendor
OSAR_ImageToVendor
YE2009_YE_PWC_Completed_Date
CollectionComments
FSRR_Required
FSRR_Required_Override
Subserviced
SubservicerName
PropertyName
PropertyStreet
PropertyCity
PropertyState
PropertyZipCode
PropertyTypeCMSA -- 'either Retail, Multi-Family, Office etc.
BorrowerName
NSF
NumberOfUnitsBed
PropertyCount
UW -- 'UW=Underwriting
OSARPropertyNumber
AssumptionDate
East-West -- 'If this Tracking Log came from the East or West office


Issues_Log Table
IssuesID
JobTrackingID
ReportingPeriod
IssueNumber -- 'this comes from the client whenever they resolve an issue
LoanNumber
ProspectusID
PropertyNumber
AssignedVendor
IssueOriginator
IssueOpenDate
WFBStatus --'if Open or Closed
VendorConsultant
Top20
UW
IssueStatus -- 'New, Open or Closed
IssueCloseDate
NextAction -- 'If Client or Us(vendor)
VendorStatus -- 'New,Open or Closed
VendorLastUpdateDate
VendorLastSubmitDate
EastWest


Invoice Table
InvoiceID
JobTrackingID
ReportingPeriod
LoanNumber
InvestorNumber
PropertyNumber
East-West
SignedOffDate --'this shows if the work has been completed
Rush24 --'if the client requested the loan to be rushed
Rush1-3 --'if the client requested the loan to be rushed


Loan_Status Table
LoanStatusID
JobTrackingID
IssuesID
DateAssigned
EntryAnalyst
EntryAnalystCompleteDate
EntryAnalystComments
OSARAnalyst
OSARAnalystCompleteDate
QC -- 'QC=Quality Control=Reviewer
ReturnForCorrectionDate --'If QCer found mistakes made by Analyst
QCComments
ResubmitforQCDate --'Date when Analyst sent back the work that had mistakes
QCCompleteDate
OnHoldForIssue -- 'Puts a Loan On Hold if can't compelete
ResolvedIssueDate
SignedOffBy --'final QCer
SignedOffDate
ConsolidatedStatement


I hope you could give me feedback with regards to how I changed the table structure, as I think there needs more change on what I did.

Let me know if you have any questions or if I need to add more information.

Any help is greatly appreciated.

Thanks

 

[iuianj07]

Okay I gave some more thought on normalizing the tables, and made some changes. Below now is what I have on my drawing board:

Job_Tracking Table
JobTrackingID
IssuesID
InvoiceID
LoanStatusID
StatusID
ReportingPeriod
InvestorNumber
LoanNumber
PropertyNumber
ProspectusID
SecuritizationDate
FirstPeriodDue
PropertyLoanStatus
WatchlistedLoan_YN
Top20
LoanBalance
FS_WFB_Received -- 'FS=Financial Statement_Client Received Date (Received from borrowers not us)
FS_VendorReceived -- 'We are the Vendor
FS_ImageToVendor
RR_WFB_Received -- 'RR=Rent Roll
RR_Vendor_Received
RR_ImageToVendor
OSAR_ImageToVendor
YE2009_YE_PWC_Completed_Date
CollectionComments
FSRR_Required
FSRR_Required_Override
Subserviced
SubservicerName
PropertyName
PropertyStreet
PropertyCity
PropertyState
PropertyZipCode
PropertyTypeCMSA -- 'either Retail, Multi-Family, Office etc.
BorrowerName
NSF
NumberOfUnitsBed
PropertyCount
UW -- 'UW=Underwriting
OSARPropertyNumber
AssumptionDate
East-West -- 'If this Tracking Log came from the East or West office


Issues_Log
IssuesID
JobTrackingID
IssueNumber -- 'this comes from the client whenever they resolve an issue
AssignedVendor
IssueOriginator
IssueOpenDate
WFBStatus --'if Open or Closed
VendorConsultant
Top20
UW
IssueStatus -- 'New, Open or Closed
IssueCloseDate
NextAction -- 'If Client or Us(vendor)
VendorStatus -- 'New,Open or Closed
VendorLastUpdateDate
VendorLastSubmitDate

Invoice Table
InvoiceID
JobTrackingID
InvoiceNumber
InvoiceDate

Loan_Status
LoanStatusID
JobTrackingID
IssuesID
StatusID
StatusDate
StatusComment


Status Table
StatusID
Status


is this table structure better than the first one? I hope I could read some feedbacks now. Thanks