Updating data from an UnNormalized table to a Normalized table

[iuianj07]

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

 

[lameid]

There is no quick an easy way of doinig this.... Without looking into your details in general you will group by what should be distinct values on the one side of your data and then append that to the table on the one side. Then you get to join the values that are equivalent to the key to the main table to pick up the unique key and append that to the many side. If you happen to have multiple 1 to many relationships to capture you might have various grouping levels. When all is done you should be able to write a query that yields the results of your source table.

Hopefully that is the guidance you needed to get started.

 

[iuianj07]

hello lameid..

thanks for the time in reading my long post and replying to it...

I kinda get what you're trying to suggest... although I know it's sometimes bad to actually spoon feed us with the right answers to our questions.. but is it possibly to actually help me out on how to do this? like how I should get started... with how I should group by what should be distinct values on the one side of my data... I do have multiple one to many relationships with how I structured my database...

Thank you for helping me out, I really appreciate it.

 

[lameid]

Ok, one of the simplest examples, you want to take all the fields other than your primary key from the target table and put them in the select statement of the source (denormalized) table.

tblLeaseType
LeaseTypeID -- PK
LeaseType -- what kind of lease is being reviewed (ex. Amendment, New Lease)

Insert Into (tblLeaseType)
Select [Lease Type]
From  tblLeaseTracking
Group By [Lease Type]

In this case I might actually add the FK to the denormalized table and update it as it is burried deep.

Does that help get you going better?