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:
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.
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.
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.
CREATE TABLE Customer…;
To create an Entity in OutSystems, first, open the Data layer in the Service Studio, then just follow the steps:
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.
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:
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.
Case scenario of an entity-relationship one-to-one.
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.
To check more detail about this, I recommend the online course Data Model Integrity.
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.
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.
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.
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.
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:
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.
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.
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.”
[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).
Have you heard about the OutSystems Developer Cloud and want to know more about what's new and what the big benefits are? Then this article is for you!
When including forms in applications, developers often wonder if they need to re-validate data on the server side after having checked it on the client. Why go to the trouble of checking the data twice?
When developing an OutSystems reactive web app, you would expect to display data quickly, but it doesn't always happen. Do you know why? We'll explore the reasons and how to tackle them.