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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Re-Architecting the table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_server_dba
Posting Yak Master

167 Posts

Posted - 12/10/2012 :  19:37:49  Show Profile  Reply with Quote
Hello Experts,

Need a suggestion with re-architecting the table...

In our Datawarehousing system, we have a customer table with million records (Customer_ID, Customer_First_Name, Customer_Last_Name, ....). Now the source system(OLTP) is renumbering all the customer_ID's and our datawarehouse system may end up with same customers with multiple Customer_ID's. Since it is datawarehouse, we need all the old numbers as well as new numbers and at the same time, we also do not want to make Customer table as Type 2. What is the best way to achieve this?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/10/2012 :  19:49:21  Show Profile  Reply with Quote
That's more of a business issue than a database one I'd say. Now you have two where previously you had one and it has the potential to get really difficult.
How do you use the customer number? On reports? On searches? For billing? Are you going to refer to the old, new or both? What do you do when you count customers for some criteria? Do you count them twice or treat the old & new IDs as the same? Can the old & new overlap?
Depending on your use, you've got a huge deal on your hands.
Anyway, the way I'd do it is allocate an internal "DW ID" and hang a bunch of external references off that, one being the original customer number and the other being the new. e.g.
DW ID, RefType, ExternalRefNumber
1000 ORIGID 123
1000 NEWID 456
(unique constraint on ref type & ref #)

Then all queries need to be rewritten in terms of the old or new identifiers.
I'd also assign my new "DW IDs" from a number range that does not already get used so you don't accidentally provide results from old queries that maybe don't know there are now multiple identifiers. Not doing this may seem like a shortcut to getting things done but I assue you, it will just muddy the waters.

Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 12/10/2012 :  20:08:10  Show Profile  Reply with Quote
Thanks for the response!!!

We are going to use it for Reports. So, we cannot report it as old and new.The new ID should be the one which will have old data too(In Reporting). There should be only one customer for any criteria and they will not OverLap(For Example...if the customer number in source changes today, all the referential integrity tables will get updated with the latest customer_ID. So there will be no OVERLAP). If we use "DW ID", aren't we storing two records for one customer? Is there a way that we can merge the old and new and create a new table with only one record(Latest Customer_ID)?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/10/2012 :  21:51:17  Show Profile  Reply with Quote
I'm afraid I don't understand what you are doing then. If it's a wholesale change then just change the IDs everywhere in the DW when you change it in the OLTP and be done with it.
If you just need a history of who changed to what and when, then a table of old/new IDs will do.
Sorry - not sure if I'm helping but I don't understand what your business problem is. If you need to change something, just change it.
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