March 11, 2011 at 9:07 am
Vague Hypothetical situation...
Our company conducts customer registrations and account information,etc,etc right. Our DBMS will have a unique id throughout our system.
However, what is best practice when/if our company gets bought out and our data needs to be merged with the new companys system who uses a different data architecture?
How are the unique ids handled? Will the parent company import the child companys data while adding a new column that will use their unique ids or what?
I ask b/c we have disparet systems that will be merged into a new application and then a few year we will buy another application that will use data from the old application. It's like those Russian matryoshka nesting dolls. Each time a new application is introduced how is the data integrity kept?
Thanks,
March 11, 2011 at 11:26 am
That's where natural keys come into play. It sounds like you've got an IDENTITY field or a GUID that's handling uniqueness for a given row, but, that doesn't define logical or business uniqueness. You still have to come up with that even if you don't use it as the primary key on the table. You should have unique constraints to enforce that otherwise, you'll get X number of values that are all the same, just with a unique ID.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2011 at 12:17 pm
Vertigo44 (3/11/2011)
Vague Hypothetical situation...Our company conducts customer registrations and account information,etc,etc right. Our DBMS will have a unique id throughout our system.
However, what is best practice when/if our company gets bought out and our data needs to be merged with the new companys system who uses a different data architecture?
How are the unique ids handled? Will the parent company import the child companys data while adding a new column that will use their unique ids or what?
I ask b/c we have disparet systems that will be merged into a new application and then a few year we will buy another application that will use data from the old application. It's like those Russian matryoshka nesting dolls. Each time a new application is introduced how is the data integrity kept?
Vague answer...
If company A is buying company B and company A systems are the ones who survive the merger then referential integrity already established in company's A systems are expected to take care of data integrity.
The actual migration of the data is a different animal. It depends of a lot of variables like, both company A and company B sell the same stuff? both company A and company B have the same customers? is former-company B operation is going to proceed independently from company's A operation? etc. etc.
In this case scenario says you are sitting on the "merged" company B therefore let company's A CIO to own the issue - you may want to make yourself available as a subject matter expert in terms of company's B systems architecture 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 11, 2011 at 12:42 pm
This is a merge issue, and I've had it happen with systems getting merged. Typically what you have to do is allow the new parent system to determine how data integrity is handled. All data from the old system (or old company), moves over and it's a pain.
In the past, I've had to build ETL processes that basically.
1. Create a new table of IDs (PKs) for existing data. This maps old to new PK values
2. Begin updating data with the new IDs, which get cumbersome in RI, but you have to work through it. Sometimes you copy (parent) data to new IDs, then update children to move over, then delete the old parents
3. Backup everything twice
4. Start deleting old data IDs
5. Run a lot, more than you think you need, checks to verify that data was moved.
6. insert into tables on new system
March 14, 2011 at 5:45 am
Thanks all. I am going to try and get a better understanding of where the issue is in our system and what can be done to resolve it. Although I don't want to step on any of our analysts toes as this is not really my job or concern. I just feel as though some analysts dont understand how a primary key works. I am going to read up on this natural key thing. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply