Chapter 2: Create database system

2.1 Database management software identification

A database management system (DBMS) is the software that stores, organises, protects and serves the data of a database to users and programs. Identifying database management software means knowing the main products on offer, what kind of data each one is built for, and what each one costs to run. A developer who can name the options and say what separates them can recommend a product with reasons instead of choosing the one that happens to be installed on a laptop.

Database software falls into two broad families. Relational DBMSs store data in tables of rows and columns, link tables through keys and are queried with Structured Query Language (SQL). Non-relational (NoSQL) systems store data in other shapes, such as documents, key-value pairs or graphs. Most business record systems, such as stock, sales and booking records, fit the relational family.

Example: a hardware shop with three branches wants one system for stock, suppliers and daily sales. Its data is made of well-defined records that must always add up, so a relational DBMS is the natural family to consider.

The products below are widely used and are met by a software developer in training and in small and medium projects. SQL examples in this chapter use MySQL 8 syntax unless another product is named.

2.1.1 Selecting Appropriate DBMS

Selecting a DBMS is a decision that is hard to reverse: once data, application code and staff skills are built around one product, changing it costs time and money. A sound selection starts from the needs of the system, not from the product name, and ends with a written recommendation that the client can read and approve.

Selection criteria

• Data model fit: Check whether the data is tabular and strongly linked (relational) or varies a great deal from record to record (document). A shop's sales and stock records are relational. A catalogue of products with very different attributes may suit a document store.
• Size and growth: Estimate the volume of data now and in three to five years, and how many users will connect at the same time. A product that handles one branch comfortably may need redesign for ten.
• Cost and licensing: Count the licence fee, the hardware or hosting cost, backup storage, and the cost of training. Free software still has running costs, and a licence that is free for development may carry fees in production.
• Operating system and hosting: Confirm the product runs on the server the client owns or can rent. Many shared hosting plans include MySQL or MariaDB, while SQL Server is chosen when the server runs Windows.
• Language and tool support: Check that the programming language of the application has a reliable driver for the product, for example PHP, Python, Java or C#.
• Skills available: A product the local team already knows is faster to build and cheaper to support than a technically better one that nobody can administer.
• Security and backup features: Look for user accounts and permissions, encryption options, and tools for backup and restore. Security design itself is covered in the chapter on database security.
• Support and community: Compare vendor support contracts, documentation quality and the size of the community that answers questions online.
• Legal and data protection duties: If the system holds personal data, the choice and its hosting location must allow the owner to meet the Data Protection Act, 2019.

Steps in selecting a DBMS

1. Gather the requirements: what data is stored, how many users, what reports, what peak loads, what budget and what deadline.
2. Write the selection criteria and give each a weight out of 100 according to its importance to the client.
3. Shortlist two to four products that can meet the must-have requirements, such as the operating system and the data model.
4. Score each product from 1 (poor) to 5 (excellent) against every criterion, using documentation, a small trial installation and the team's own experience.
5. Multiply each score by the weight of its criterion and add the results for each product.
6. Test the leading product with a small prototype: create the main tables, load sample data and run the three or four most important queries.
7. Write the recommendation, stating the scores, the risks and the running costs, and have the client approve it.

Worked example: the hardware shop uses four criteria with weights that add up to 100: cost 30, ease of administration 25, fit with its PHP web application 25, and local skills 20. The scores below are the developer's judgement for this shop only.

Weighted total for MySQL = (30 × 5) + (25 × 4) + (25 × 5) + (20 × 4) = 150 + 100 + 125 + 80 = 455, which is 4.55 out of 5.

Weighted total for PostgreSQL = (30 × 5) + (25 × 3) + (25 × 4) + (20 × 2) = 150 + 75 + 100 + 40 = 365, which is 3.65 out of 5.

Weighted total for SQL Server Express = (30 × 4) + (25 × 4) + (25 × 3) + (20 × 3) = 120 + 100 + 75 + 60 = 355, which is 3.55 out of 5.

MySQL scores highest for this shop, so the developer builds the prototype on MySQL. A different shop with different weights, for example one whose staff all know SQL Server, could reach a different result with the same method.