In the previous modules, we introduced the concept of databases, discussed the types of data they store, and practiced sending REST API requests to retrieve data. At the same time, we remained an outside participant in the process and only requested information from various sources.

It's time to create your database! In this module, we will do just that, we will understand how data is stored in the database and how they can be interconnected. But first of all, let's start with the theory. Let's deal with the form in which the data comes to us, as well as with what categories the databases are divided into according to the data structure.

Data Basics

Data representation

The absolute leader in data representation in the REST API is the JSON format. In all the examples from the previous modules, we received data in this format. It is worth recalling that REST does not impose restrictions on the choice of format for us, in the future you will surely meet others (for example, XML). At the same time, due to its light weight and easy human readability, developers often prefer JSON.

JSON (JavaScript Object Notation) is a text-based data exchange format based on JavaScript. And don't let the JavaScript in the title fool you. The JSON format, although it originated from this programming language, is completely independent of it and can be used anywhere.

Let's see what a JSON object consists of and how it is written.

All the data you received was enclosed in curly braces “{}”. They are always placed at the beginning and at the end of the JSON object.

The object itself consists of a set of records, which are “Key : Value” pairs and are separated from each other by commas “,”.

The key is the name of the entry itself, enclosed in quotation marks “”. Examples: “name”, “value”, “region”, “address”. It can be any word, the main thing when developing is to make sure that this meaning is clear.

Values ​​can be of various types. Let's consider them all.

  1. String. Contains text information, a set of characters in the Unicode standard. Strings are enclosed in quotes “”.
  2. Number. It can be either integer or floating point. It is written as is, it is not necessary to enclose quotes.
  3. Boolean. One of two values. Either true or false. Like a number, it is written without quotes.
  4. Array. An ordered set of elements. Each element can be of any type. An array is enclosed in square brackets “[]”, and its elements are separated by commas.
  5. Object. The JSON value can be another JSON object. The same rules apply to it as to the root object. It is also enclosed in curly braces and contains its own set of records.

Look at the data you received in the first modules with this information in mind. Select the JSON components, determine what type the received values ​​belong to.

Storing data

We've dealt with JSON. Now we pass to the main thing — databases. Data can be stored in them in a variety of ways. At the same time, it has historically developed so that the relational database model has received the greatest distribution.

When using the relational model, data is stored in the form of tables, with a specific set of data, whose structure is rigidly specified at the database design stage. The description of a data structure in relational databases is called a schema. It defines the composition of the tables, the structure of the fields in these tables, as well as the relationships between them.

The DBMS uses the SQL language to manage data with a relational model.

SQL - Structured Query Language. This is a declarative language, which means that its commands describe only the necessary action (find data, delete them, change), and each DBMS decides for itself how to perform it.

There are many different relational DBMSs. Among the most common are Oracle, MySQL, MS SQL, PostgreSQL. By the way, AppMaster uses PostgreSQL, which means that it uses a modern advanced DBMS that works in a huge number of different organizations and is also free software (that is, you don’t need to pay extra money for using it).

Have you noticed the presence of the SQL abbreviation in almost every DBMS name? Actually, an alternative name for a relational database is an SQL database.

However, there is an alternative approach. Non-relational databases, or NoSQL. It is worth noting that No in this case is not a negation of “no”, but an abbreviation for Not only. That is, “Not only SQL”.

Non-relational DBMSs do not use a common query format (like SQL), each of them implements its own way of working with data.

They do not require a uniquely defined data storage structure. The data itself is stored in them not in the form of strict tables, but in the form of objects with an arbitrary set of attributes (much like JSON). This may be relevant when working with data whose structure is subject to frequent changes.

At the same time, due to its free structure, NoSQL solutions are easier to scale if you need to create a database distributed on multiple servers.

Examples of NoSQL DBMS include MongoDB and Redis.

Database design

It's time to design your own database. To do this, go to the tab Data Design (Data Designer) on the left panel.

The data in the database is stored in the form of special tables (models). And you can notice that we already have one model. It is part of the authorization module and is included in every project by default. Thanks to it, new users of the application are created and existing ones are managed. But we will not dwell on its study now, but will create our own model.

Imagine that we are developing a map service. Let's create a model that contains information about countries. To create it, you need to right-click in an empty area of ​​​​the canvas and select Create empty model.

To create, we only need to specify the name of the model. We will deal with auto-generation of endpoints and user interface elements in further modules of the course.

Please note that immediately after creation, the model already contains 4 fields. These are system fields, the presence of which greatly simplifies the initial creation and further use of the model.

  1. ID (integer) - Unique identifier, primary key. It is automatically created for each new entry in the table and is intended to ensure that there are no duplicates. It is by ID that you can uniquely identify a record in a table. Its value starts at 1 and automatically increases by 1 for each new entry.
  2. CreatedAt (datetime) - The time the record was created in the table.
  3. UpdatedAt (datetime) - The time the entry was last modified.
  4. DeletedAt (datetime) - The time the entry was deleted. Of course, only if soft-removal was used. That is, such a deletion, when the record is only marked as deleted and filtered by requests for access to it, but at the same time physically remains in the table. This is different from bulk deletion, which actually deletes the data completely.

In addition to the system ones, it would be wise to add custom fields to the created model. Suppose we want to see the name of the country and some description with information about it.

Choosing a field type shouldn't be a problem. For the name, String is suitable, and for the informational description, Text.


In addition, four more switches are available:

  1. Multiple values ​​(Array) - use arrays instead of single entries.
  2. Not null - the specified field cannot be empty, it must always contain data.
  3. Unique - the value of the field must be unique, in this model there cannot be two records whose values of this field are the same.
  4. Index - indicates that a special index will be created for this field in order to speed up the search.

In general, it's only right to check marks if it's really necessary. For example, we could mark Not null and Unique for country names, assuming that there can't be a country without a name, or two countries with the same name. However, it is a good idea to control this at the stage of creating the logic of the application, and not put restrictions on the database itself.

Similarly, create a table with information about cities. Think about what data fields it can contain, what type these fields are.

The data in the database does not exist on its own, in the form of scattered tables. They are related to each other in a certain way. The key to developing a data model is to define these relationships and build relationships.

To establish such links, it is necessary to draw a line with the mouse from the border of one model to another. In our example, we know for sure that each city is located in some country, so we can create a link from country to city.


There are 3 different types of connections:

  1. One-to-one (has one). Each record in the table is mapped to one record from the associated table (this is also true in reverse). A simple example is a person and their passport. We can always be sure that this connection is unique. A passport can only have one holder, and each person can have only one valid passport.
  2. One-to-many (has many). Each record in one table can have many records in another table. Our database is a similar example. A country can have many different cities, but each city can belong to only one country. This is the connection we will make.
  3. Many-to-many. A relationship in which multiple records from one table can correspond to multiple records from another. A simple example is the relationship between teachers and students. Each teacher can teach many students, just as each student can learn from many different teachers.

Homework

Imagine that you have to develop an application for an online store. Create a database model for it to work.

  • It is necessary to provide for the availability of goods with cards of their description, various categories of goods, information about orders and about customers.
  • Populate the tables with fields of various types (use at least 5 types).
  • Establish relationships between tables. Use all 3 link types.