SQL databases: which type to choose and when to use it

Almost every application, website and company system stores data somewhere, and most often there is a database behind it. The word SQL is heard constantly, yet few know which type of database is suited to what. Let us explain it practically, including the question of whether Microsoft Access still makes sense today.

What a database is and what SQL is

A database is an organized store of data that can be read from and written to quickly and reliably. Imagine it as a very clever table that can handle millions of records and link them together.

SQL (Structured Query Language) is the language we use to talk to a database. Through SQL we ask for data, add it, change it and delete it. Databases that use this language are called relational (SQL) databases, because data in them lives in linked tables.

Relational (SQL) versus NoSQL

To start, one important distinction:

• Relational (SQL) databases store data in tables with a precise structure and relationships between them. They are reliable, proven and suitable for most ordinary applications.
• NoSQL databases have a looser structure and suit specific cases, such as huge volumes of data, fast caching or unstructured data.

For the vast majority of companies and websites, a relational SQL database is the right choice. NoSQL is a solution for specific needs, not the default choice.

The most common SQL databases and when to use them

SQLite, small and embedded

SQLite is a lightweight database stored in a single file, without a separate server. It is part of many applications and phones.

• When to use it: smaller applications, mobile apps, local tools, prototypes, websites with low traffic.
• When not: when many users access the data at once or high performance is needed.

MySQL and MariaDB, the workhorse of the web

MySQL (and its open twin MariaDB) is the most widespread database for websites and applications. It has a huge community behind it and a large part of the internet runs on it.

• When to use it: websites, e-shops, content systems, ordinary company applications. This relates to the article on how to build a quality e-shop or website.
• When not: when you need the most advanced features for complex data.

PostgreSQL, advanced and robust

PostgreSQL is a powerful and very reliable database with advanced features. It is favored by projects where accuracy and more complex work with data matter.

• When to use it: more demanding applications, analytics, projects with complex data and relationships, long-term solutions.
• When not: for a completely simple website it tends to be needlessly robust, although it does no harm.

Microsoft SQL Server, the corporate standard

MS SQL Server is a database from Microsoft for the corporate environment, often combined with other Microsoft products.

• When to use it: companies built on the Microsoft ecosystem, enterprise applications and systems.
• When not: for small projects and websites it tends to be needlessly large.

Microsoft Access, yes it too has its place

And now to Access, which many people ask about. Microsoft Access is a database for one or a few users with its own environment for building forms and reports. It is not a full database server, but rather a tool for an individual or a small office.

• When it makes sense: small records, simple agendas, personal or office databases, where one or a few people work and neither high performance nor remote access is needed.
• When rather not: when several people need to access the data at once, when the data grows, or when it is a critical company agenda. Then it is more sensible to move to a database server (for example MySQL, PostgreSQL or MS SQL Server).

So Access is not dead, but you need to know where its limits are. Many companies start with it and eventually outgrow it.

A brief comparison

What to keep in mind when choosing

• How many users and how much data? This determines whether a file database is enough or a server is needed.
• Where will it run? A database server usually runs on a server, local or cloud.
• Backups. Data is the most valuable thing, so think about backups from the start.
• Integration with other systems via API.
• Future growth. Choose so the solution lasts even as the company grows.

Conclusion

There is no single best database, there is a suitable database for a given situation. For small things SQLite or Access is enough, a website and e-shop are well served by MySQL, more demanding projects by PostgreSQL and companies in the Microsoft environment by MS SQL Server. The important thing is to choose based on the number of users, data volume and plans, not on what is currently popular.

Wondering which database to choose, or need to move from Access to a proper database server? Get in touch, we will advise and design a tailored solution.

This article is part of our Software and system overview.