Suitability of MySQL and SQLite? I am about to build a C# wpf application. I need to easily port the application e.g. one click installation. I am not sure should I use SQLite or MySQL db? The range of data won't run into billion at most it maybe go upto few millions? What is the best suggestion here? I will have both write and read functionality with also transaction controls for some operation.
The two aren't necessarily mutually exclusive, I have built apps which use MySQL for the production database, but used a SQLite database for testing, because of SQLite's ability to run a database in memory.
i.e. prior to every test, you can build the schema from scratch, seed the database with data and then run your test, once the test finishes the SQLite database is flushed from memory and you can start from scratch with your next test.
You obviously need to be careful that you use code and features that are portable between the 2 systems - MySQL has lots of features that are specific to it for example, ALTER COLUMN - and re-positioning a column after an existing column wont transfer to SQLite.
Because its hard to anticipate the future, you could start out on SQLite and stick to portable SQL as much as possible and then if you find you find you can't achieve your goals on SQLite, move to MySQL.