4

I am going to create a SaaS application in PHP. In that application the user can create and manage multiple tables to extend functionality. After user finish with the application he can download php code and database.

We will also provide sql import functionality so the user can create schema from (.sql) file.

I search on google but not found any proper solution. You can consider sqlfiddle functionality here.

I have 2 options in my mind but need better solution: 1) For creating multiple database and its tables, use table prefix as a solution 2) Convert mysql to sqlite. At the time of download create export as mysql (.sql) file.

It can have aprox. 10,000 users/databases. Please suggest a solution to provide each user a seperate database if any.

If shared server will not work I will purchase VPS. The main requirement is to provide each user their own database.

Vaibhav V. Joshi
  • 175
  • 3
  • 14
  • One obvious solution would be to create one sqlite file per user. Is there a reason you cannot go with the simplest solution? – nvoigt Nov 22 '17 at 08:56
  • Thank you. There could be 10k sqlite files. Is it okay for normal server? If purchase VPS what should be the configuration? – Vaibhav V. Joshi Nov 22 '17 at 11:14
  • 1
    10k files is nothing. If your VPS can handle the load, I cannot tell you. Talk to your VPS vendor and maybe have a proof of concept ready. Shouldn't be too hard to write a 30 lines program opening 10k files and executing statements in parallel. See how it works. – nvoigt Nov 22 '17 at 11:24
  • I'm so sorry for simple question. Do your users run projects on your server? Meaning that databases on your server have any data or just meta data? – Gholamali Irani Nov 27 '17 at 11:08
  • Yes the users will run projects on our server. The code and some db schema can be customize. After all done user can download project files and db and run on their own server. – Vaibhav V. Joshi Nov 27 '17 at 16:26
  • Hard question, you mentioned all possible answers in your question – Gholamali Irani Nov 28 '17 at 13:25
  • Okay thanks. Was want to make sure with the solution. – Vaibhav V. Joshi Nov 29 '17 at 13:42

1 Answers1

0

I am going to choose sqlite as a choice for db. After doing some benchmark sqlite seems good option for DDL and DML operation.

I will use mysql to sqlite .sql converter: https://github.com/sutara79/convert-mysql-to-sqlite

To improve the speed I follow following stackoverflow post: Improve INSERT-per-second performance of SQLite?

Vaibhav V. Joshi
  • 175
  • 3
  • 14