Just wondering if anyone is developing web apps that serve a number of customers? If so, what is the optimal way to maintain the data, assuming that it will be limited… say less than 20 mb each.
I can think of a couple ways to do this. Maintain all the data in one file and simply have a field which designates which customer you are working with so you only access their data.
Or, create a separate database for each customer and store them in a separate folder.
I tend to think the separate folder is ideal but just want to see what other developers are doing.
We’ve developed dozens of desktop apps that maintain data for an unlimited number of clients and those apps always create separate databases for each client.
If all the customers are using the same app and functions to access and store data then absolutely store all the data in one DB and have a field to designate customer ID, using it to serve up and store data based on the customer. This will make your DB queries consistent across the app.
This is especially true if the data structure is consistent at an app level, versus customer level.
You’ll also be able to gather metrics easier such as total records, customers, etc. without having to query multiple databases and having to create a new DB every time you acquire a new customer.
If you need to change the data structure or add a column, you’ll need to do it in one place in a single DB rather than adding to x number of unique customer DBs.
Having a DB for each customer sounds like a nightmare to maintain and there are no technical advantages to having multiple copies of the same DB structure.
Thank you for your reply. Perhaps I can point one reason for separating the data into separate db’s.
We already have the code to update the data structures if they change and I’ve ported it from a desktop app to the web. But think of a scenario where the database does need to be updated and you have all the data in one DB. You’ll have to ensure all users are out of the data file before you can update it. If they each have their own, you can simply disconnect from their DB, update the file structures, and then reconnect. I actually have already done this and it works seamlessly.
One of the first apps that we wrote was a database manager where we store file structures for all our commercial apps. We then incorporated a process in our apps to determine if the file structure needs to be updated and does so without any intervention by us.
Another situation that may arise is if someone has a problem with their data and you are asked to intervene and perhaps make a change to it for them. This might even need to be done by downloading the data file and making changes to it, then uploading back to the server. Again, we’ve done this more times than I can count for our desktop apps. As much as we’d like to think we can control what a user enters in our apps, very often they will find a way to either enter something that doesn’t belong or enter it in the wrong place. That’s when they call us.
I do appreciate your opinion, but I’m just trying to consider what has worked for us on a desktop level and ensure that we won’t need to do the same thing with a web app.
It sounds like you have already decided the current architecture will work then.
However, this is not typical, or optimal, of how SaaS data applications are structured though. Think of any banking application, or even existing SaaS apps (task managers, project management, etc.), Shirly they do not have unique DB for each customer. I think even Xojo probably uses a single database to store all their customer data.
For database updates most simply pull the app offline during off-peak hours, do your updates and reenable. I see and do this quite often. My banking app often is unavailable at 2:00 am - “down for maintenance”. Some database actions to update won’t even require this.
If you need to make changes to a specific customer’s data, simply include the customer’s unique ID in the query to update the single database. This goes with importing as well.
If you have a hundred customers and want to add a column to the database(s) “secondary contact” you will need to update 100 different databases? What if the same app accesses that secondary contact column, but it didn’t get added to their database?
Trying to maintain multiple databases that have same data structure, but the only reason they exist is because the customer ID is different sounds very messy. If you’ve created a bunch of backend processes to try and mitigate it then go for it, but no, that is not typical, nor advisable.
Believe me, I hear what you are saying and I’m not necessarily disagreeing with you because doing it the way you described is definitely easier than the process that we’ve developed internally. If I explained how we are doing it, you’d probably say it’s a lot of work and your way would be much easier, and I’d agree with you. But in terms of what we know works and the amount of effort we’ve put in to make it work, it seems like anything less than that would lead to potential problems.
I think it’s safe to say that most developers would not do things the way we have, so we are used to others questioning our methods. That’s been happening for the last 40 years and our user base continues to grow.
I’m going to continue to assess your comments before I move forward. I’m really just getting my feet wet with web apps and everyone’s opinion matters at this point.
When you ask people to disconnect because you need to do work on the db, you can be disapointed. I remember the time I was a support agent for a desktop application and I needed everybody out for some maintenance - and no way to force disconnect them - it sometimes happened that people would not quit the application, and I had to postpone the work, more than one time . Users are … users. I suggest that you incorporate in your software a way to broadcast a message, let say 2 times during the day, with a modal window needing them to click OK. I some don’t dismiss the message, that means they are away, and if they left the office without quitting with unsaved data, well it was their responsability to quit.
I know I seem harsh, but users need to be disciplined : when they are finished, they should quit the application, not just in case some maintanance has to be done, but this way they free a slot someone else may use.
Your 2:00AM might be a client’s 10:00AM if is based in another timezone, applying an update can be annoying for them.
Avoids noisy neighbors. If one client has a huge amount of data, it won’t directly impact another client’s performance. Noisy neighbors can be easily migrated to another server if needed.
You don’t have to worry too much about having to ensure data won’t be mixed between clients, you’ll only be able to pull data from that client.
It’s true that applying a database migration is harder, but isn’t like you’ll be running a query one by one. It can (must) be automated.
I agree it might be overkill for most applications, but one database per client can be useful in some scenarios.
Just wondering if anyone is developing web apps that serve a number of customers?
Yes, we are.
We prefer a single database, unless a customer is interested in on-premises or white-labelling our platform for higher-usage (the noisy neighbours Ricardo mentioned).
In our web apps we have developed an admin / management webpage where we as app administrators can:
prevent logins we set a value in the database that is checked during the sign-in process (it doesn’t prevent sessions which naturally start as you arrive at the web address of your app)
if you wish to prevent sessions (from memory) set App.AcceptingConnections = False
list active sessions including the current webpage, username, email, ip etc of active users (we also classify our web pages by their impact on the database [ to understand if the user is potentially performing anything “impactful” on the database ] , tags like reads-db, writes-db etc - help manage any potential impact (if any) on the database.
broadcast a message to a single or all active sessions
end a selected session or all sessions.
In practice sessions either starting or ending lag a little in reporting but otherwise this works great in managing down-time.
I incorporated a timer that if the application is dormant for 2 minutes it will display a message requiring them to click a button or it shuts it shuts them down.
I developed a CRM that stored all user data in one MySQL database. It was a mistake since once it got very large it slowed down and was a huge pain to backup. I now use two CubeSQL servers, one for all the common databases (non-company) and one for all the company silo databases.
Trivial to setup, super fast, no clogging of data searches, easy backups, trivial to download a company database and work on/repair it locally, user admins can backup their database offline anytime for peace of mind.
For database changes, I keep the version number of the app as a field in the database. If the app is later than the database, it checks if there are any changes to the structure and updates it. I have a minimum app version number stored in the database as well, so really old apps cannot connect and must be updated.
John, I neglected to mention that effective management of cold users and cold data, can make a very big difference to your overall database requirements and therefore cost.
Archiving users and data due to inactivity is a good strategy, as is deleting the accounts and data of users who have cancelled subscriptions (after checking any legal requirements in respect of the retention of such data).
For example, we may migrate user accounts and data to an archive server after 180 days of inactivity, leaving it archived but accessible. If the account becomes active again (passes a usage threshold) it may be migrated back to the main db, but generally being archived is a place from which few accounts return.
If you have run a SAAS platform for even 1-2 years, this can represent a significant % of your data.
Did it slow down as a result of Xojo’s web/cloud architecture or the DB size? Certainly, there are massive databases that have millions or even billions of records that are very web responsive. Was the slow down due to query time, because this should be really fast.
If on Xojo web/cloud and all the different customer databases are on the same server, what technically makes having numerous DBs faster? I suspect it is not the query but the multiple users on a single server and that would drag things down.
One possible downside with having one DB per customer is that you will then need to open a separate DB connection per session. You may end up with quite many open DB connections to both multiple and single DBs.
If you only have one DB you can get away with opening a single DB connection on the app level that is used for all sessions and users.
I’ve decided that one database per client is the way to go. Of course, that database might be used by more than one user. It will not be a heavily used app unless there are lots of users but they will be using it for a relatively short time. Just to send out notifications.
However, I’m now thinking about how to modify the file structure if it is needed. I already have the code ported from our desktop apps to use an FSM (file structure master) file to determine if a file structure modification is necessary and it will happen when a user accesses a database that needs to be updated. The big question is, the database cannot be updated unless no one is in it. Our desktop apps do this but they are primarily single-user applications so we’ve never run into a problem.
What is the best way to keep track of the number of users in a database in a web app or do I just have to warn the user that no one else should be in the database during an update or do I just tell them it needs to be update and make them go to a formal “update” page and then throw up all kinds of warnings before updating?
. Users are … users. I suggest that you incorporate in your software a way to broadcast a message, let say 2 times during the day, with a modal window needing them to click OK. I some don’t dismiss the message, that means they are away, and if they left the office without quitting with unsaved data, well it was their responsability to quit.