Hello guys,
I am currently in the process or re-structuring one of our current databases. We currently have an aweful unnormalized table structure that I changed the table structure to hopefully a properly normalized table structures
I think I did an okay job in normalizing the tables, I am now in the process of updating the data from the unnormalized to the normalized tables...
To have a quick overview, this is a simple Job Tracking database, wherein our client is a bank, and they send us loans that we need to review and complete for them... the main purpose of this database is to keep track of all the loans we received and what phase has it already been... (phase which mostly are dates.. meaning -- loan received, loan assigned, loan entry completed, QC completed, loan sent back to client -- meaning it has been completed, loan invoiced, loan On hold, etc...)
Now my main question... is there a quick way for me to update the normalized table structure from the unnormalized table? The db currently have 4,700+ records and it'll be difficult to manually update the normalized table structure...
Here is the db's old unnormalized table structure and my new normalized table strucure: -- this could be long...
UnNormalized table
tblLeaseTracking
Situs ID
Turnaround
Date Assigned to Analyst
Situs Analyst
Date Entry Complete
Process Document Issue
Process Document Issue Comment
Date Process Document Issue Sent to Client
Date Process Document Issue Resolved
Date Invoiced
Loan Number
Tenant Name
Lease Type
Lease Additional Info
Client Office
Date Received
Lease Documentation
Property Operating Statement
Rent Roll
Property Inspection
OSAR
Tenant Financials
Other Documentation
Initial Receipt Document Issue
Initial Receipt Document Issue Comment
Date Initial Receipt Issue Sent to Client
Date Initial Receipt Issue Resolved
Rush Deal
Situs QC Analyst
Date QC Complete
Date Submitted to Client
Lease Consent Withdrawn
Lease Consent Percentage
Lease Consent Withdrawn Comment
WFB Asset Administrator
Current Turnaround Date
Link to Folder
Internal Comments
Rent Reduction
Space Reduction
Below Weighted Average Rent
Above Weighted Average Rent
Term Extension
Relocation
Early Termination
Short Term Lease
National T
Anchor T
Property Type
Other
Expansion
EnterTime
RushSameDay/1day
Rush2-5day
ResolvedTime
NORMALIZED TABLE STRUCTURES:
tblJobTracking
SitusID -- PK
LoanNumber -- Text
TenantName -- Text
LeaseType -- FK to tblLeaseType
LeaseAdditionalInfo
ClientOffice -- FK to tblClientOffice
ClientAssetAdministrator -- FK to tblClientAdministrator
LinkToFolder -- Hyperlink
InternalComment -- Memo
RushDeal -- Yes/No
Rush2to5Days -- Yes/No
PropertyType -- FK to tblPropertyType
DateInvoiced -- Date/Time
tblDocumentTracking -- purpose: to track if the client sent us the important documents needed for us to review the loan
DocumentTrackingID -- PK
SitusID -- FK to tblJobTracking
LeaseDocumentation -- Yes/No
PropertyOperatingStatement -- Yes/No
RentRoll -- Yes/No
PropertyInspection -- Yes/No
OSAR -- Yes/No
TenantFinancials -- Yes/No
OtherDocumentation -- Memo
tblLeaseAttributes -- users will check if what kind of lease attribute the loan they are reviewing.
LeaseAttributesID -- PK
SitusID -- FK to tblJobTracking
RentReduction -- Yes/No
SpaceReduction -- Yes/No
TermExtension -- Yes/No
Relocation -- Yes/No
etc...
tblLeaseType
LeaseTypeID -- PK
LeaseType -- what kind of lease is being reviewed (ex. Amendment, New Lease)
tblLoanStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Status -- FK to tblStatusChange
Analyst
StatusDate -- Date/Time
StatusComment -- Text
tblPropertyType
PropertyTypeID -- PK
PropertyType -- what kind of property are they reviewing (office, retail etc...)
tblQualityTracking -- to check quality of work of the entry analysts by the QC Analysts
QualityTrackingID -- PK
SitusID -- FK to tblJobTracking
RentalAmounts -- Yes/No
LeaseTerm -- Yes/No
etc..
tblStatusChange -- different phase/status that a loan goes through
StatusChangeID -- PK
Status -- Text
tblClientOffice
ClientOfficeID -- PK
ClientOffice -- Text (ex. West office, East office)
tblClientAssetAdministrator
ClientAssetAdministratorID -- PK
ClientAssetAdministrator -- Text
As you can see, I've managed to create 10 different tables from
the one giant table.
My problem now is how I could update the data from the unnormalized to the normalized data...
I could give an idea of how it should be updated:
UnNormalized
SitusID LoanNumber TenantName DateEntryComplete DateQCComplete
111 222 Tname1 7/1/2011 7/3/2011
DateSubmittedtoClient
7/3/2011
Normalized
tblJobTracking
SitusID LoanNumber TenantName
111 222 Tname1
tblLoanStatus
DealStatusID SitusID Status Analyst StatusDate StatusComment
1 111 1 Name1 7/1/2011
2 111 2 Name2 7/3/2011
3 111 3 7/3/2011 Sent Back
I apologize for the lengthy post,
Any help or guidance is greatly appreciated.
Thank you
I am currently in the process or re-structuring one of our current databases. We currently have an aweful unnormalized table structure that I changed the table structure to hopefully a properly normalized table structures
I think I did an okay job in normalizing the tables, I am now in the process of updating the data from the unnormalized to the normalized tables...
To have a quick overview, this is a simple Job Tracking database, wherein our client is a bank, and they send us loans that we need to review and complete for them... the main purpose of this database is to keep track of all the loans we received and what phase has it already been... (phase which mostly are dates.. meaning -- loan received, loan assigned, loan entry completed, QC completed, loan sent back to client -- meaning it has been completed, loan invoiced, loan On hold, etc...)
Now my main question... is there a quick way for me to update the normalized table structure from the unnormalized table? The db currently have 4,700+ records and it'll be difficult to manually update the normalized table structure...
Here is the db's old unnormalized table structure and my new normalized table strucure: -- this could be long...
UnNormalized table
tblLeaseTracking
Situs ID
Turnaround
Date Assigned to Analyst
Situs Analyst
Date Entry Complete
Process Document Issue
Process Document Issue Comment
Date Process Document Issue Sent to Client
Date Process Document Issue Resolved
Date Invoiced
Loan Number
Tenant Name
Lease Type
Lease Additional Info
Client Office
Date Received
Lease Documentation
Property Operating Statement
Rent Roll
Property Inspection
OSAR
Tenant Financials
Other Documentation
Initial Receipt Document Issue
Initial Receipt Document Issue Comment
Date Initial Receipt Issue Sent to Client
Date Initial Receipt Issue Resolved
Rush Deal
Situs QC Analyst
Date QC Complete
Date Submitted to Client
Lease Consent Withdrawn
Lease Consent Percentage
Lease Consent Withdrawn Comment
WFB Asset Administrator
Current Turnaround Date
Link to Folder
Internal Comments
Rent Reduction
Space Reduction
Below Weighted Average Rent
Above Weighted Average Rent
Term Extension
Relocation
Early Termination
Short Term Lease
National T
Anchor T
Property Type
Other
Expansion
EnterTime
RushSameDay/1day
Rush2-5day
ResolvedTime
NORMALIZED TABLE STRUCTURES:
tblJobTracking
SitusID -- PK
LoanNumber -- Text
TenantName -- Text
LeaseType -- FK to tblLeaseType
LeaseAdditionalInfo
ClientOffice -- FK to tblClientOffice
ClientAssetAdministrator -- FK to tblClientAdministrator
LinkToFolder -- Hyperlink
InternalComment -- Memo
RushDeal -- Yes/No
Rush2to5Days -- Yes/No
PropertyType -- FK to tblPropertyType
DateInvoiced -- Date/Time
tblDocumentTracking -- purpose: to track if the client sent us the important documents needed for us to review the loan
DocumentTrackingID -- PK
SitusID -- FK to tblJobTracking
LeaseDocumentation -- Yes/No
PropertyOperatingStatement -- Yes/No
RentRoll -- Yes/No
PropertyInspection -- Yes/No
OSAR -- Yes/No
TenantFinancials -- Yes/No
OtherDocumentation -- Memo
tblLeaseAttributes -- users will check if what kind of lease attribute the loan they are reviewing.
LeaseAttributesID -- PK
SitusID -- FK to tblJobTracking
RentReduction -- Yes/No
SpaceReduction -- Yes/No
TermExtension -- Yes/No
Relocation -- Yes/No
etc...
tblLeaseType
LeaseTypeID -- PK
LeaseType -- what kind of lease is being reviewed (ex. Amendment, New Lease)
tblLoanStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Status -- FK to tblStatusChange
Analyst
StatusDate -- Date/Time
StatusComment -- Text
tblPropertyType
PropertyTypeID -- PK
PropertyType -- what kind of property are they reviewing (office, retail etc...)
tblQualityTracking -- to check quality of work of the entry analysts by the QC Analysts
QualityTrackingID -- PK
SitusID -- FK to tblJobTracking
RentalAmounts -- Yes/No
LeaseTerm -- Yes/No
etc..
tblStatusChange -- different phase/status that a loan goes through
StatusChangeID -- PK
Status -- Text
tblClientOffice
ClientOfficeID -- PK
ClientOffice -- Text (ex. West office, East office)
tblClientAssetAdministrator
ClientAssetAdministratorID -- PK
ClientAssetAdministrator -- Text
As you can see, I've managed to create 10 different tables from
the one giant table.
My problem now is how I could update the data from the unnormalized to the normalized data...
I could give an idea of how it should be updated:
UnNormalized
SitusID LoanNumber TenantName DateEntryComplete DateQCComplete
111 222 Tname1 7/1/2011 7/3/2011
DateSubmittedtoClient
7/3/2011
Normalized
tblJobTracking
SitusID LoanNumber TenantName
111 222 Tname1
tblLoanStatus
DealStatusID SitusID Status Analyst StatusDate StatusComment
1 111 1 Name1 7/1/2011
2 111 2 Name2 7/3/2011
3 111 3 7/3/2011 Sent Back
I apologize for the lengthy post,
Any help or guidance is greatly appreciated.
Thank you