Difference between MySQL/SQLite/etc databases?

Question

This is my first time trying to work with a database, so bear with me.

I need to write a program that will use a database that I do not have access to yet. I know there is MySQL, SQLite, and a bunch of other SQL things, but I'm not sure what the difference between them is. Do I need to know what kind of database it is before I can use it (i.e., is a MySQL .db file different from a SQLite .db file?), or is the file itself going to be the same and the difference is how it is accessed?

EDIT: I am programming for an Android tablet, that probably matters. But I will not be creating the database, it will be given to me and I have to work with it.

Solution 1

You might want to run through a view tutorials creating a MySQL database, and a SQLite instance to see how they work and how you can easily interact with them.

The access to each involves adapters, notwithstanding creating the tables you'll need, the user access (username / password / role to access the table).

Here's a straight-forward sqlite tutorial: http://zetcode.com/db/sqlite/

Here's a staight-forward sql with C# tutorial: https://zetcode.com/csharp/mysql/

I remember being overwhelmed with databases my first time. My advice is to start with something well-supported, with a nice community, and search results a-plenty of tutorials, where you can grasp the fundamentals.

Then, based on your application requirements, and platform constraints, you can determine where to pivot. But reviewing the differences between databases without actually having worked with them is like trying pieces of sushi without ever eating fish.

EDIT:

If you're building on Android, yep, learn SQLite.

I highly recommend this tutorial to help you see SQLite used in an Android project: https://developer.android.com/training/data-storage/sqlite

For data abstraction / ORM, you should familiarize yourself with Content Providers and Cursors (http://developer.android.com/guide/topics/providers/content-providers.html)

Good Querying!

Solution 2

First off, you'll need to know what type of database it is before you can connect to it as you'll need to use the appropriate database driver for your platform. mySQL, Oracle, Postgres, SQL Server, etc will all use different drivers as the binary protocol used to talk to them (authenticate and transfer information back and forth) is different.

As far as your actual SQL code, it depends. For the most part, you can count on SELECT * FROM Foo working with any SQL compliant database on the planet. However, once you start doing anything more complicated or using non-standard keywords, you might run into trouble. For example, some databases support the USING keyword for JOINs and some don't.

One thing you might look into is an ORM. This will allow you to abstract the actual SQL dialect from your program and then (for the most part) not have to worry about the actual SQL. Some popular ORMs are ActiveRecord (for Ruby on Rails) and Entity Framework for .NET. Hope this helps!

UPDATE:

Since the Android tag was just added to the post, I did a quick search for SqlLite ORMs that support Android and came up with this. Maybe worth checking into, or perhaps someone can comment on whether it's any good :)

Solution 3

Database Abstraction Layer is the strategy of having a smart middleman so you can program without knowing exactly which database system you have underneath. Database Abstraction isn't as utopian in practice as it sounds, and most people end up programming directly for a particular database. You will likely find that the more experience you get with your database of choice, the more you wish you knew its particular nuances better. So the short answer is there is enough difference to matter!

If you use a software architectural pattern like Model-View-Controller, it can enable you to do a lot of your programming irrespective of the database you use. Taking Model-View-Controller as an example, you could program your Controllers and your Views without knowing what database you are using.

The limitation to what you can do before deciding on a database system is the inconvenient fact that good programming practice calls for fat Models and skinny Controllers. So a big part of your actual programming effort is held hostage by your choice of a database solution.

If I didn't know what database would be used, I would start programming my View and then my Controller. If PHP is your language, you may find a CodeIgniter tutorial enlightening.

Solution 4

An article about "Appropriate Uses For SQLite"

http://www.sqlite.org/whentouse.html

Summary：Checklist For Choosing The Right Database Engine

1. Is the data separated from the application by a network? → choose client/server
2. Many concurrent writers? → choose client/server
3. Big data? → choose client/server
4. Otherwise → choose SQLite!

View more solutions

Comments

[jdhao]

The link to MySQL tutorial is broken.

[Dominic Tancredi]

Thanks @jdhao - I have updated it with a new URL

[jdhao]

I mean http://zetcode.com/databases/mysqlphptutorial/ is broken. 😢

[Dominic Tancredi]

Ok I don't see his tutorial for it, but I updated it with the C# example

[Jan Bodnar]

The old tutorial covered a discontinued driver for MySQL. The updated ones are: zetcode.com/php/mysqli & zetcode.com/php/pdo