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.
Where can you find your 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.
Below the Database, exists three modules: System, OutSystems Charts, and OutSystems UI. Once you create your application module, the platform automatically adds these three modules as dependencies.
The Module System has a User entity, that usually is the one to be used when you want to configure or fetch End Users of your application. The OutSystems Charts and OutSystems UI have a group of Entities with some static information that will help you define and configure the properties of Charts or other Visual elements, called Widgets, in your application. In case you do not need the connection, or you will not consume any of the data derived from these modules, you can remove it from the dependencies.
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.
How to create an Entity in OutSystems?
CREATE TABLE Customer…;
To create an Entity in OutSystems, first, open the Data layer in the Service Studio, then just follow the steps:
- Right-click on the Database icon. A menu will open with tasks you can execute in your database;
- Select the option “Add Entity”;
- In the Entity properties, change the name of your entity to “Customer”;
- As a best practice, write a detailed description of the goal of your Entity.
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.
How to add attributes to an Entity in OutSystems?
CREATE TABLE Customer( LastName varchar(255), FirstName varchar(255), City varchar(255), PhoneNumber int);
To add an Attribute to an Entity, first, open the Data layer in the Service Studio, then:
- Right-click on the Entity name. A menu will open with the tasks you can execute in your Entity;
- Select the option ‘Add Entity Attribute’;
- In the Attribute properties, change the name to ‘LastName’;
- 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;
- It is also possible to configure the maximum length for this field. For that, change the default value of 50 characters to 255 characters;
- Now, you need to repeat the process for the other attributes.
Besides the Name and the Datatype of the Attribute, there are other properties, like if the field is mandatory when inserting or updating the record in the database. In case it is not, a default value for the attribute can be defined.
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.
What are the rules of a Primary Key in OutSystems?
As an Identifier of a Record, it is necessary to guarantee uniqueness. In case the data type of the Identifier attribute is Long Integer or Integer, and the Is Auto Number property is set to Yes, the platform will define the number of the next record based on the last created number.
However, besides the numeric data type, an Identifier can also be defined as a Text attribute. In this scenario, it is necessary to create the logic to fetch the last created attributed and program the new identifier associated with the record that is being saved.
Also, considering that the relationships between tables in SQL can be defined as One-to-one (an extension of the original table), it is possible to set a Primary Key to the same Identifier of the original table.
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.
How to create a Foreign Key?
To create a Foreign Key is necessary to add a new attribute of type Entity Identifier to an Entity. This new attribute will point to the respective primary key of the other table.
As the example above demonstrates, it is possible to identify the owner (Customer) of a Pet and the Gender of that Pet through the Referential Attributes. If a relationship between the Entities is not mandatory, the Mandatory property of the Reference Attribute must be set to No. Moreover, it is possible to guarantee Integrity when a Primary Key is deleted by defining delete rules for data stored in these entities.
To check more detail about this, I recommend the online course Data Model Integrity.
There is one constraint when creating a Reference Attribute inside a Static Entity. Following the definition of Static Entities, records can not be changed at runtime.The same happens with the values of the relationship. So basically, a Static Entity can only reference another Static Entity.
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.
To see the entities right-click on the diagram editor or drag and drop your entity or group of entities to the 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.
The Veterinary Clinic Entity Diagram is composed of seven Entities and two Static Entities (Consultation Type and Gender) and respective relationships.
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.
Below you can find a list of some available components to improve the features of your databases, with respective descriptions:
- Web Previewer
- Preview files from your database or an external URL without downloading.
- https://www.outsystems.com/forge/component-overview/1281/web-previewer
- OutSystems sample data
- A module with sample data to accelerate app prototyping and development. This system module contains entities and sample data (such as employee names, accounts, products, transactions, and much more) to accelerate app prototyping and development.
- https://www.outsystems.com/forge/component-overview/4145/outsystems-sample-data
- DB cleaner on steroids
- This component helps you keep your database neat and tidy, based on DBCleaner and Logs Management.
- https://www.outsystems.com/forge/component-overview/5018/db-cleaner-on-steroids
- Ciphered Local Storage Plugin (exclusive to Mobile apps)
- This plugin enables you to keep your mobile application’s sensitive data safe using a ciphered Local Storage database.
- https://www.outsystems.com/forge/component-overview/1500/ciphered-local-storage-plugin
- Key Store Plugin (exclusive to Mobile apps)
- This plugin allows your application to securely store secrets such as usernames, passwords, tokens, certificates, or other sensitive information (strings) on iOS & Android phones.
- https://www.outsystems.com/forge/component-overview/1550/key-store-plugin
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.
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.
- Integration Studio Documentation
- https://success.outsystems.com/Documentation/11/Reference/Integration_Studio
- Course: Integrating with External Databases
- https://www.outsystems.com/learn/courses/119/integrating-with-external-databases/
- Documentation: Integrate with an External Database
- https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database
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.”
References
[1] Agung Wahyudi, Adiska Farhani, Marijn Janssen, Challenges and Opportunities in the Digital Era, vol. 11195, pp. 504, 2018.
[2] ORACLE (2020). Database – What Is a Database?. Consulted on 2020, October 30 on: https://www.oracle.com/database/what-is-database.html#:~:text=A%20database%20is%20an%20organized,database%20management%20system%20(DBMS).
