Importance of Data

Many articles on the web explain why quality data is fundamental to the survival of Companies. An article published on Challenges and Opportunities in the Digital Era on IEEE[1] reinforces that companies cannot live without data by giving some examples of its importance in the evolution of medical science, management, and transport services or research fields. These articles further emphasize that today’s organizations aim to be mostly data-driven in their decision-making processes by collecting relevant data to extract patterns and facts and utilizing those facts to influence their business decisions.

It is paramount developers have an overall knowledge of the Business Concepts and Processes to implement top-notch solutions. Consequently, the definition of the data model is critical when developing a product or solution. It is the foundation of any robust, responsive, and scalable system.

Too, this is no different in the OutSystems Platform. While other programming paradigms have different frameworks and tools to design the data model, OutSystems has its own way to create the data model. 

In this article, we map the well-known Structured Query Language (SQL) to the OutSystems Platform. In the process, we will explain where the data lies in the Platform and showcase some advanced solutions on how to import or communicate with an existing application’s data source.

Quick-note: the examples in this article were developed in an OutSystems Web Reactive application, using the Service Studio version 11.8.12, based on SQL Server. If you would like to know more about the OutSystems Platform and Service Studio developer tool, please follow the links:

Data modeling Concepts

Before showing a big picture of a Data Modeling example, first, let see some of the most common concepts of a Relational Database Management System inside an OutSystems application.

Mapping Concepts

Database

The definition of Database didn’t change over time. Oracle’s website defines it as: an organized collection of structured information, or data, typically stored electronically in a computer system [2]. Parallelly, in OutSystems, the collection of data is also stored in a database. 

Once you create an application in OutSystems Service Studio,  OutSystems low-code and visual development environment, you will automatically have a Database for your application that runs inside the OutSystems Server. To find your OutSystems database, you will need to open the Data layer, as displayed on the image.

#1: Service Studio - Data Layer

#1: Service Studio – Data Layer

Tables

A table, as a database object, represents a collection of related data entries arranged into columns and rows. In OutSystems a table is represented by an Entity.

To create an Entity in OutSystems, first, open the Data layer in the Service Studio, then just follow the steps:

  1. Right-click on the Database icon. A menu will open with tasks you can execute in your database;
  2. Select the option “Add Entity”;
  3. In the Entity properties, change the name of your entity to “Customer”;
  4. As a best practice, write a detailed description of the goal of your Entity.

#2: Customer Entity – Properties

Table Columns

The collection of data entries inside a Table is arranged into columns and rows. In OutSystems, a table column is represented by an Entity Attribute.

Once you have created an Entity, the Service Studio will give you an error message, because it doesn’t make any sense to create a Table without any Columns to insert data.

#3 – An Entity without editable attributes error message: “Invalid Entity: Customer cannot be exclusively composed of an attribute with ‘Is AutoNumber’ set to ‘Yes’.”

To add an Attribute to an Entity, first, open the Data layer in the Service Studio, then:

  1. Right-click on the Entity name. A menu will open with the tasks you can execute in your Entity;
  2. Select the option ‘Add Entity Attribute’;
  3. In the Attribute properties, change the name to ‘LastName’;
  4. OutSystems infers the attribute’s Data Type based on its name. However, it is recommended that you check if the data type is correct. In the case of varchar, the data type will be Text;
  5. It is also possible to configure the maximum length for this field. For that, change the default value of 50 characters to 255 characters;
  6. Now, you need to repeat the process for the other attributes.

#4 – LastName Attribute and its Properties

Primary keys

To identify a Record in an Entity, you need to point to its Primary Key. Once you have an Entity created, Service Studio automatically adds an Id attribute to your entity to identify each one of its records.

#5- Identifier as Long Integer

#6- Identifier as Text

#7- Identifier as another Entity Identifier (Foreign Key). 

Case scenario of an entity-relationship one-to-one.

Database Relationship

Foreign Keys

Foreign Keys act as a cross-reference between tables since it references the primary key of another table. In OutSystems, the Foreign Keys are called Reference Attributes.

#1: Service Studio - Data Layer

#8 – Two Entity Identifiers (Foreign Keys): GenderId and CustomerId.

#9 – Two Reference Identifiers (Foreign Keys) of type Gender Identifier and Customer Identifier and respective properties

Relationship Types

In a Relational Database, you can define the Relationship Type between the Entities as one-to-one, one-to-many or many-to-many. In case you are curious to learn more about this topic, I recommend the only course Modeling Data Relationship, available on the OutSystems Learning Website.

Data modeling Example

After your data modeling is complete, it is possible to create an Entity Diagram, which allows to have a full picture of your data modeling.

#10 – Add an Entity Diagram

#11 -Drag and Drop entities into Diagram

Veterinary Clinic Diagram

Using a Veterinary Clinic as an example, we will define an Entity Relationship Diagram representing the business needs. 

In this scenario, the Veterinary Clinic schedules health appointments with different purposes, like a Routine consult or an Urgency (Consultation Type – Static Entity). The consultation is led by a Vet who diagnoses the Pet’s general health and prescribes a treatment. For each consultation, it is also possible to proceed with several exams and assign the results. 

Each Pet is associated with only one owner (Customer), and besides the usual attributes, it is possible to define the Gender of the Pet, like Female, Male, or Undefined.

#12 -Veterinary Clinic Entity Diagram

What else?

Are you an SQL developer?

Check the course Getting Started with OutSystems for SQL Developers on the OutSystems Website. This course introduces OutSystems to SQL Developers. It provides an introduction to the OutSystems Platform and a mapping to the most common operations related to data handling when Developing OutSystems applications.

Forge components

The OutSystems Forge is a repository of reusable, open code modules, connectors, UI components, and business solutions to speed up app delivery time and improve your database performance and logic. 

One important thing to have in mind is to bea careful when choosing a Component or Plugin to install in your environment. Try to check for OutSystems Supported or Trusted components, like the one in the image below. Make sure to inspect and test the code before sending it to your production environment.

#13 – Forge filters: OutSystems Supported and Trusted components

Below you can find a list of some available components to improve the features of your databases, with respective descriptions:

Of course, there are many more. Explore other components or plugins to improve the features of your application’s database, just search on the Forge page.

But I already have an External Database

It is possible to consume an External Database instead of creating a new one. To do that, you need to use the OutSystems Integration Studio. Integration Studio is a desktop tool that allows you to create and manage extensions to bridge your application and physical database.

#14 – Integration Studio: Connect to External Table or View Wizard

If this is a topic that interests you, visit the links below that will help you learn the basic features of the Integration Studio and how to integrate your External Database with your applications.

Conclusions

As explained above, it is straightforward to map the concepts of a Relational Database to an OutSystems Database. You can create Tables as Entities and Columns as Attributes, and define the relationships between the database model concepts. 

Furthermore, you can create your database model from scratch or reuse components and plugins to improve the features and manageability of your database. Finally, it lets you integrate your application with your External Databases. 

Now that you have learned the basic OutSystems Database concepts, I highly recommend you further your education. But you are not alone in your journey to achieve advanced knowledge: start exploring the Database world inside the OutSystems with the support of great documentation and courses, and the OutSystems community. Plus, consider becoming an active part of the community by sharing your support and fresh ideas to improve the “Data model world.”