On Tue, 2006-04-18 at 14:32 +0300, Achilleus Mantzios wrote: > Thanx for your thoughts, but this would require touching > 173 tables + > 2,594 SQL statements in a sum of 324 programs (which sum into 125,085 > lines of code) We did a very similar conversion to the one proposed here a couple of years back, and switched an old Java app from a dedicated schema-per- client model to a shared schema with company ID model. I figured I'd share our results, to at least give you a data point on what you could expect if you went that route. At the time, the application consisted of roughly 90 tables and 90,000 lines of code. Never counted the SQL statements there were probably very roughly 1000. We had the advantage of having the 90,000 LOC spread across only two larger applications instead of a bunch of small programs. It actually ended up being a much easier process than we had expected, because the application logic didn't really need change all that much. The major steps were: 1. Add an extra company FK to the top-level objects in our data model. For example, we added companyId to the userdetail table, but not to the address table which depends on the userdetail table. In our case, this meant that we needed to add a column to about 30 (one third) of our tables. Not very difficult. 2. Inserts into those 30 tables were modified to include an extra parameter. The actual insert change is trivial, but depending on your plumbing it might take some refactoring to get the companyId to the code that does the inserts. This involved a moderate code refactoring, but the changes were very straightforward and not too prone to errors. 3. Because we didn't support moving from one company to another, the update and most of the select logic didn't change at all. 4. Needed to update all of the search queries to only search within the current company. Fortunately, this type of query is relatively rare. In our case, there were only about 20 that needed updates out of our 1000 queries. The change is also simple, just add one AND to the WHERE clause. 5. Security checks. This is the only step that took a good chunk of time, because our application is web-based and so a mischievous user could muck with id's in the HTTP POSTS on pretty much any page in our application. This meant that we needed to be careful to always do company checks before processing any user input. Fortunately for us, at the same time we were enhancing our security model anyway, so doing the extra company check really added no extra time on top of the work that needed to be done anyway. Steps 1-4 took a single developer 3 days to implement. Step 5 was harder to measure because as I mentioned we bundled it together with a larger security overhaul, but to perform it separately (and do an audit to make sure it was correct) would have taken perhaps a week or two. Anyway, overall in our case we had a pretty successful conversion. It ended taking significantly less time than anticipated, and the decreased maintenance of only managing one database and the ease of building new global administrative and reporting tools definitely made the project worth while. -- Mark Lewis
