dexdev (9/15/2014)
Hi,Hope you guys can help...
We created an application that is using multiple dbs, one for each client using the application. All dbs are identical, each db holds the client's data, they all use the same tables and structure.
For convenience, we then decided to change our model and use one single db for ALL clients. So, we added columns to identify the client to all tables.
All is fine till now, but now we are trying to merge all our current clients, who have each their own db, into this new one db, but we're not sure how to best go about it.
Any suggestions?
We thought of doing it manually and import each client and each table at a time, but this would take forever.
Thanks
G.
Quick questions,
1. is the referential data, that is the non-client specific data the same (i.e. location_id = 1 is NewYork )?
2. will you have to expand primary and foreign key definitions due to overlapping sequence (identity) values?
3. have you evaluated and assessed the impact of multiple clients in a database designed for a single client? This can lead to some nasty triangular joins and hefty performance penalties.
I have hardly ever come across a single subject(client) database design which scaled painlessly up to multiple subjects, most common problems being Cartesian produce (set multiplications) and loss of performance.
To properly advice on the matter, one needs at least a mid level ERD and basic numbers (cardinality/distribution etc.)
😎