June 5, 2013 at 10:49 am
Hi there,
My company has several instances of the same app. One app/database per client. Now maintenance has become a nightmare. I want to combine all into one. The problem is how to merge tables with the some identical keys. What is the best practice? Sorry if it's not clear, I'm not a SQL person.
Any suggestion would be helpful.
June 5, 2013 at 11:34 am
That sounds like a lot of work to do when you think about it.
You should either add a Client ID to each table to preserve uniqueness when merging all the databases. You must add this to your code every time you call one of these tables.
Another option would be to add a prefix (to char based keys) or a base number (to int keys). But this will change all of your keys and have to review the code.
You won't find an easy option for this project and it might take you a while to test everything.
An option would be to automize the administration. It won't fix the problem but it will give you more time to implement the changes.
June 5, 2013 at 12:15 pm
Thanks for quick reply. Is it a good idea if I reindex tables starting from some number before merging? Let's say in the first database table Entities Entity_Id is in a range from 1 to 1000, so I will reindex table Entities in the second database to a range from 1001 to 2000? Or this is a stupid idea?
June 5, 2013 at 12:29 pm
But you have to check all of your dependant tables to update the values. An option is to check your Foreign Keys are specified as ON UPDATE CASCADE.
It's hard to go into many details, because I might be giving an inadecuate solution due to the lack of information (and I'm not sure that a forum will give you complete answers to this scenario), so you better test before dealing with the real data.
June 5, 2013 at 12:34 pm
Assuming you have down time, my suggestion would be:
Merge all your tables into one table with no constraint, with an additional column indicating the source database/table it came from. Add a new IDENTITY column (OR UNIQUEIDENTIFIER if your going that route). Then you can simply update your ancillary tables with the new ID, joined on the old ID. Then drop the source column and old ID and its done.
This isnt that hard, and you would need downtime to accomplish it.
June 5, 2013 at 12:50 pm
DiverKas (6/5/2013)
Assuming you have down time, my suggestion would be:Merge all your tables into one table with no constraint, with an additional column indicating the source database/table it came from. Add a new IDENTITY column (OR UNIQUEIDENTIFIER if your going that route). Then you can simply update your ancillary tables with the new ID, joined on the old ID. Then drop the source column and old ID and its done.
This isnt that hard, and you would need downtime to accomplish it.
Is not that hard for a single table, but as far as I understand, the idea is to migrate all tables from multiple databases.
June 5, 2013 at 1:05 pm
luis covered a couple important points i wanted to mention again: you'll need a new column for the ClientId in there somewhere.
we've done this in the past, and the way we tackled it was adding temporary columns to the end of the tables, that capture the old PK and FK values.
we insert into one table that has an identity() column for the new PK, and make sure the previously existing PK gets placed in the temp columns. with that in place, we can insert into child tables, by getting the new PK from the table and joining it against the migrating table(s) against the previous PK's;
our database didn't have data going deeper than three levels of PK/FK relationships, but we still had to migrate EVERY tables in FK hierarchy order, and also, comparing lookup tables(like user defined statuses) against values instead of PK, so the table TBCITY might have the value 'Miami' in two databases
but with different PK's assigned to them. those tables, and the values that reference them, need to point to a new combined table instead.
when it's all migrated, we can drop the columns that were holding old PK's.
Lowell
June 5, 2013 at 1:51 pm
Thanks a lot to all.
It is still foggy but at least I can start with something in mind.
June 23, 2013 at 10:38 pm
First create a development environment. Attach all the database in a local machine in a single instance. Now take time and have plan for the identical keys. May be add field for new key and populate.
June 26, 2013 at 8:48 am
tania 56573 (6/5/2013)
Hi there,My company has several instances of the same app. One app/database per client. Now maintenance has become a nightmare. I want to combine all into one. The problem is how to merge tables with the some identical keys. What is the best practice? Sorry if it's not clear, I'm not a SQL person.
Any suggestion would be helpful.
I'd suggest adding Client_ID to all of your tables. So instead Order_ID being the primary key for Orders table and foreign key on Order_Items tables, it would now be a composite key of Client_ID, Order_ID. The primary advantage of this is that the existing key values don't change, which will retain transactional history. Also, since consolidating data across clients is being done in the physical model for maintainability reasons (the client entities themselves are not merging in the real world), the case usage and queries of the application will tend to be client specific.
For example, client ABC, or perhaps the sales representabive for client ABC, will login to the application and want to see sales transactions only for ABC. Having Client_ID in your transactional tables will help facilitate this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply