Would you recommend this design? Having each user have their own sqlite database rather than putting all user data in a single MySQL database? I know the most efficient route would be MySQL, but is the method I’m talking about inherently bad?
Yes, no maybe… who knows? You need to give us some kind of context before we can give you any advice. Is it a web app, desktop app or a mobile app? Do users need to share data etc…
The client side will be desktop and mobile apps. On the server end it will be a web app with an api that the clients will access to add and retrieve the user’s data. User data will be accessed from multiple devices (possibly at the same time) i.e. a computer and smartphone
Guess ur concept isn’t much clear since you didn’t explain where data will stand and be.
Only on server in DBMS (MySql) or on user device (mobile and/or workstation) or both.
Will be data sync on server-client and client-server side and so on.
On your last post in this thread guess your targeting something like web storage (cloud storage) which is present now days in form of onedrive, dropbox, g-drive and so on.
Data will only reside on server. It will work like any typical client-server database app. Client display data and takes user input, and the server stores the data. web app is in the middle for the api (retrieve/send data).
Separate SQLite db’s for each user definitely keeps their data separate from everyone else. User goes away the database goes away. We’ve done a couple of projects like this. Everything is pretty simple once the user is logged in.
But I could argue the MySQL route as well. It really depends on the requirements of the application and the client (assuming not you).
Then on MySQL DBMS create db structure which will hold user accounts and make one table where you will store data of each user.
Create web service which will interact with client end app and you don’t need to sqlite dbms on client side if you don’t want to permit offline data use and review and after all doing sync between server and client side.
Make more concrete what you want to achieve and you will get more clear answer from us.
Perfect, this is what I was looking for. I was sure this method was used in practice, but I wanted to double check.
Everyone else thank you for your responses as well, very helpful.
One downside I see with separate DBs is that once you have more than a handful of users and want to make a structural change to the DB; add a table, column, index etc. it becomes a bit more complicated. You have to make the changes to everyone’s individual DB.
Say you want to add some columns or table to support more features; the new app you deploy may expect that column/table to be present, if not, it will have to create the column/table in the individual users DB. To me, this adds complexity to the APP to ensure it doesn’t bomb out and ensure it updates the user’s DB to the new specification.
Personally I prefer one DB with user data identified with a unique ID. Manage the DB on the server with a good DBMS. Make the changes to the single DB then deploy the new app that supports the features.
[quote=401055:@Joseph Evert]One downside I see with separate DBs is that once you have more than a handful of users and want to make a structural change to the DB; add a table, column, index etc. it becomes a bit more complicated. You have to make the changes to everyone’s individual DB.
Say you want to add some columns or table to support more features; the new app you deploy may expect that column/table to be present, if not, it will have to create the column/table in the individual users DB. To me, this adds complexity to the APP to ensure it doesn’t bomb out and ensure it updates the user’s DB to the new specification.[/quote]
If this is a web app there’s no issue with deployment. And keeping track of the database version isn’t that big of a deal. When the database is opened, check the version and then run your update script(s) to make it up to date. We do this ALL the time with both desktop and web applications in Xojo.