INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Updating data from an UnNormalized table to a Normalized table

Updating data from an UnNormalized table to a Normalized table

(OP)
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

RE: Updating data from an UnNormalized table to a Normalized table

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.

RE: Updating data from an UnNormalized table to a Normalized table

(OP)
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.

RE: Updating data from an UnNormalized table to a Normalized table

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)

CODE

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close