Alex avatar image
Alex asked

Using a schema per office.

situation: I'm working on a financial application that's meant to be used in a single-user way. Right now it uses MS Access but it can easily use SQL Server when I just switch the connection string. Since some users are sharing the same Access database with multiple users, I've already added a crude mechanism to avoid locking errors. Basically, all data is stored in documents, with a document table linked to dozens of others. When a user opens a document, data is read from all related tables into an XML document and the document record is marked as "Opened by...". When it's saved, all tables are updated and the document is put back to the "Closed" state. This works quite well, even for a customer who has up to 15 users working simultaneous, on Access. So it should work well with SQL Server too.

Customer request: But recently, a large organisation asked me if they can have up to 500 users sharing a single database. My first thought was "WOW!"... Then I asked for additional requirements. And basically, they have about 200 offices with 2 or 3 persons in every office, who all use their own MS Access database. But system administrators would prefer to see all data in a single database.

The problem: The main problem is that employees of one office are not allowed to see data from the other offices. So if all data is stored inside a single database, I would need to add an additional system to my code to manage users and offices and then connect documents to offices or whatever. It would be a lot of work which could introduce additional bugs to my code. Worse, I have thousands of users and most just use it on a small scale. Only this user wants to use it on a big scale. They are willing to pay for the adjustments but it would have impact for the other users too.

Solution I consider: Still, after some research I decided that I could solve this problem without modifying the code of my application. I can create multiple schema's in the database. Every schema would be linked to a single office and have a single database user/login which will look at this schema by default. Since the login information can be specified in the connection string, the application would immediately look at the right schema after login. A test with SQL Server 2005 and four schema's proved to me that my code would work just fine. All it would need is an experienced DBA to set it all up. Or a special management tool which would automate the tasks of managing offices within the database.

Basically, I would end up with about 200 database users, logins and schema's and 200 different connection strings. All schema's would contain the same table structures and not much else, since I only use it as a data store. (MS Access was also used for just the table functionality and nothing else.) There would be no change to the code and their own DBA could easily manage it themselves. They could even implement this whole solution by themselves if they want to! The current version of my application already supports SQL Server. (Btw, the user accounts that I create will have very limited access: select, insert, update and delete on all tables within their own schema, no rights outside the schema.)

I am very experienced with SQL in general. (started in 1988 with SQL, when the language was still limited to the "Select" command.) I have good experiences with SQL Server 2005 and I'm not afraid to learn new things. I'm not a DBA, though. And I'm also inexperienced with such a multi-schema solution. (I never needed more than one.) So I need some help with this.

My question Considering the problem and the solution I'm suggesting, would you choose for the same solution? Not just as a quick-fix but also for a long-term solution? Are there any useful sites with do's and don't in relation with schema's? I've also suggested this as solution to the big customer but the people whom I talked to did consider it practical but also lacked the experience to tell me if this is a good idea or not.

If there's a reason why I should not do this, I would like to know this right now.

Note: the application is developed as a Delphi 2007/WIN32 application and uses raw ADO commands to connect to the database.
The main reason why this customer wants everything in a single database is because of several maintenance tasks. A single database is easier to backup. It's also easier to upgrade when needed, since everything is centralized in a single database. Right now, they're working with 200 MS Access databases, which is an even worse nightmare. Basically, it's an improvement if things go from real worse to slightly bad. :-) There will be future improvements, where I will modify the database to support users and offices in a better way but this process takes a lot of time. This solution is quick to implement and technically, all it needs is some way to manage these schema's. But I can't estimate if this solution will be replaced within the next to years or if it's going to last for a long time.

And yes, I realize that maintenance is a nightmare. Maintenance of 200+ MS Access databases is worse, though. It's a quick solution to bring some stress relieve to an unhappy customer. It's also irritating to create the amount of objects every new office would need, but I already have a solution that can create a complete SQL Script to create a new office, including login, tables and user. Creating this tool wasn't difficult, because the database originated as an MS Access database and isn't very complex. It has primary keys but I never added any relations between the tables since relations are maintained within the XML document that's used internally by the application. An additional management tool can be developed to maintain all those offices and I've done some testing and am confident that I can automate most -if not all- the tasks for maintaining these offices.

And the customer did indicate that they would like to do some data mining but the application is importing/exporting data to another application for exactly that purpose. So data mining is a nice-to-have function.

There is another drawback when I move all data in a single database, instead of keeping them separate. The documents in the MS Access database are unique per database, but users can import/export data to other databases. As a result, some data is likely being maintained by more than one office. (And yes, they are aware of this! It's done on purpose.) I would need to add additional intelligence to my code just to keep the documents unique per office, if I don't use schema's.

Still, this could just end up as a temporary solution. It would solve the problem right now instead of over 6 months, when I'm done implementing the new functionality plus anything else on the wish-list.

Update I've been testing a bit more and created an SQL Script with a single variable for the office name. I then created a batch file which invokes SQLCMD.exe to execute this SQL script for 300 different offices, with 300 different names. The database used for this test is SQL Server Express (2005) and it seems to run just fine. So, while maintenance could be a bit of a nightmare, I think it can be managed with a good set of SQL Script for all maintenance actions.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

One reason that you should not do it is that maintaining that would be a total nightmare. You really want to design something that means you don't have a whole load of extra schemas / objects to create when you get a new office coming on-line.

Another is that presumably the customer wants one big database in order to be able to run reports across all offices. Those reports will then be 'funky' to say the least - they will have massive UNIONs from 200+ schemas, and will all need modifying each time a new office comes on board.

Basically taking a single user app and turning it into a segmented multi user app is not a simple thing. I can only emplore you, for the love of all things chocolatey and good - please don't do it by creating hundreds of schemas in one database.

One thing you haven't mentioned in your question is why the customer wants all users in one database... Perhaps we can help you look at a solution for the bigger picture?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jim Orten avatar image Jim Orten commented ·
This sounds like a buy now, pay later application. The client (potential client) is always wanting it to do more now but not willing to pay the price up front for proper design. So they end up paying the price on the backend (maintenance). If this is a useful and needed application (sounds like it is), then it will likely continue to be useful and needed well beyond what anyone expects - and will end up costing many times more in maintenance then what it would have cost to do it right the first time. (kind of like a credit card)
2 Likes 2 ·
Fatherjack avatar image
Fatherjack answered

I think I would explore a solution around views based tables to restrict specific users to specific data. If a view is selecting data for SalesAreaXYZ and only the users from SalesAreaXYZ have permission to that view then they will have secure access. Dont let any users access a table directly, only via views that they are authorised to select from.It will be, as you have described it, a total nightmare to manage as there will be multiple views/procs etc that will need very tight security controls applied but it would, in theory, give the effect that you require.

From a global reporting respect, simply by-pass the views and go to the whole table(s) for 'global' reporting. This would make the reporting a lot easier than creating scripts that collect data from the same tables in all the schemas.

The down side of this is that the application would need to be changed for each site to collect data from their newly created views eg XYZ area app would need to select data from uvw_XYZ_Sales and ABC area app would need to select data from uvw_ABC_Sales where both areas currently select from Sales table.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
No one wants to tell you the ugly truth so I will. Your approach needs to be re-worked, the data model needs to be redesigned, reading all of the data into an XML dataset is not the answer. Moving to SQL Server from Access you have a tremendous opportunity to do it right but you are just applying more band-aids and making more administration/maintenance work for yourself. I know budget constraints rule the day, hopefully it is not cost prohibitive to make it right. The other posters bring considerable xp to the table, it would behoove you to seriously consider their input.
1 Like 1 ·
Scot Hauder avatar image Scot Hauder commented ·
I know it is a lot of work, I've had to do it myself but you will thank us in the long run. The luxury you have which I didn't, is you know the system, data and user expectations. You can improve this.
1 Like 1 ·
Alex avatar image Alex commented ·
(+1) Secure access isn't a problem. The current MS Access version has no security whatsoever and no one needs it. Using views instead of schema's might work but would require a datamodel change. Right now, I'm using a simple tool which just creates the SQL Create script for SQL Server based on the MS Access database to keep both synchronized. The code still needs to support MS Access for the single-user versions.
0 Likes 0 ·
Alex avatar image Alex commented ·
The biggest problem is that for now, the same application needs to continue to support MS Access. I don't want to force all other users to migrate to SQL Server and I don't want to maintain two different versions of the application. It will need to be reworked but I also need to keep current users happy with it. This application is over 10 years old already and always used MS Access. I'm trying to migrate it to SQL Server but I also need to add new functionality with every update.
0 Likes 0 ·
Alex avatar image Alex commented ·
I know everything should migrate to a better datamodel in the end. However, migrating data from a multi-schema database to a single-schema solution is easier than migrating the same amount of MS Access databases to a single schema. The use of an XML Dataset was done so users can us the application disconnected from any database. It's done by design about 3 years ago when we weren't considering this migration.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.