SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Query on Database Normilization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clinton_eg
Yak Posting Veteran

India
60 Posts

Posted - 03/14/2012 :  09:50:32  Show Profile  Reply with Quote
Team

I had a query which i need help with.

Senario: Say i create a normalized database, as per my understanding if i change the name of an analyst' s manager in the Headcount table it will:

1) Change the manager name in all tables which reference the manager field in the Headcount table

2) it will affect historical data also.

Requirements:
i am fine with point 1 but not point 2 as i don't want this to affect the historical data.

Request your expert opinion's on this. How should i avoid historical data from being affected.

P.S - Please suggest any book, online tutorials which will help me understand the database development concepts and best practices.

Thanks

Ewan Gilby

Edited by - clinton_eg on 03/14/2012 09:51:38

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/14/2012 :  11:08:32  Show Profile  Visit russell's Homepage  Reply with Quote
1. No it won't. It will change the manager name in result sets of queries. If the manager name exists in other tables, then it isn't exactly normalized.

2. History should NEVER be allowed to change. Historical records are often flattend out (de-normalized) for this very purpose. Also, some records should never be allowed to change. Instead, add a bit column indicating status (active/inactive), then add NEW records when certain things change.
Go to Top of Page

clinton_eg
Yak Posting Veteran

India
60 Posts

Posted - 03/14/2012 :  14:00:52  Show Profile  Reply with Quote
quote:
Originally posted by russell

1. No it won't. It will change the manager name in result sets of queries. If the manager name exists in other tables, then it isn't exactly normalized.

2. History should NEVER be allowed to change. Historical records are often flattend out (de-normalized) for this very purpose. Also, some records should never be allowed to change. Instead, add a bit column indicating status (active/inactive), then add NEW records when certain things change.


Hi russell, regarding point 1 what i mean was i will have 2 or more views which do a join with the headcount table to retrieve the manager names.

regarding point 2, could you provide me refrences to any study material which will explain your concept in detail.

Ewan Gilby
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000