Hello and welcome to modeling data relationships with OutSystems.
Hello and welcome to modeling data relationships with OutSystems.

This is a follow-up to modeling data, so if you haven't watched that lesson yet
This is a follow-up to modeling data, so if you haven't watched that lesson yet

please do so before proceeding.
please do so before proceeding.

When we talked about data modeling, we described it as the process of mapping
When we talked about data modeling, we described it as the process of mapping

concepts of your application into data types. Now, data types are rarely
concepts of your application into data types. Now, data types are rarely

isolated, they normally are connected in some way, they are related in some way
isolated, they normally are connected in some way, they are related in some way

and in this particular lesson we will use a data model around the concepts of
and in this particular lesson we will use a data model around the concepts of

books and authors and publishers to describe how these relationships can be
books and authors and publishers to describe how these relationships can be

obtained in the OutSystems platform. Before we go into the specifics of
obtained in the OutSystems platform. Before we go into the specifics of

OutSystems
OutSystems

let's talk a little bit about how data is referenced in general terms, in
let's talk a little bit about how data is referenced in general terms, in

databases. You have the concept of a primary key, this is a way to uniquely
databases. You have the concept of a primary key, this is a way to uniquely

identify a particular record of your data type. Now, primary keys can be of two
identify a particular record of your data type. Now, primary keys can be of two

kinds, they can either be a simple primary key, where a single attribute on
kinds, they can either be a simple primary key, where a single attribute on

that database table dictates the uniqueness of the record, or a composite
that database table dictates the uniqueness of the record, or a composite

primary key, whereby a collection of columns in the database table dictates a
primary key, whereby a collection of columns in the database table dictates a

unique record. Now, from the viewpoint of a second data type that wants to
unique record. Now, from the viewpoint of a second data type that wants to

reference a data type that has a primary key, you have the concept of a foreign
reference a data type that has a primary key, you have the concept of a foreign

key. The foreign key, on a record of the second data type, will hold the value of
key. The foreign key, on a record of the second data type, will hold the value of

the primary key, on the record of the first data type, that it wants to
the primary key, on the record of the first data type, that it wants to

reference. If for some reason you do not want to reference a record on the first
reference. If for some reason you do not want to reference a record on the first

data type, there's a special value that the foreign key can have, which is the
data type, there's a special value that the foreign key can have, which is the

Null, indicating that this particular record doesn't bear an equivalent on the
Null, indicating that this particular record doesn't bear an equivalent on the

first data table. Now, in OutSystems, to implement a primary key, you need to have
first data table. Now, in OutSystems, to implement a primary key, you need to have

an identifier attribute. This is created for you automatically by the
an identifier attribute. This is created for you automatically by the

platform but you can adjust its type to be an integer, long integer, or
platform but you can adjust its type to be an integer, long integer, or

text. These attributes are always mandatory and in the case that you opt
text. These attributes are always mandatory and in the case that you opt

for an integer based
for an integer based

identifier, it will be auto-numbered, meaning that every time you insert a new
identifier, it will be auto-numbered, meaning that every time you insert a new

row into the table of this entity it will also generate the new ID. It is
row into the table of this entity it will also generate the new ID. It is

important to note that only simple primary keys exist in OutSystems, so you
important to note that only simple primary keys exist in OutSystems, so you

can only have one single attribute to index into a certain entity, so you
can only have one single attribute to index into a certain entity, so you

cannot implement composite keys as we've seen in the first slide. To implement a
cannot implement composite keys as we've seen in the first slide. To implement a

foreign key in OutSystems, you use a reference attribute in the second entity
foreign key in OutSystems, you use a reference attribute in the second entity

that you have, and it needs to be of the data type of the identifier, of the first
that you have, and it needs to be of the data type of the identifier, of the first

entity that you want to point to. By setting these mandatory, you will be able to tell
entity that you want to point to. By setting these mandatory, you will be able to tell

the database that it should enforce a non-Null value every time a new record is
the database that it should enforce a non-Null value every time a new record is

inserted. When creating an attribute, if you name it
inserted. When creating an attribute, if you name it

EntityNameID, by the rules of the name matching and the accelerator that we
EntityNameID, by the rules of the name matching and the accelerator that we

described in the other data modeling lesson, you will immediately get an
described in the other data modeling lesson, you will immediately get an

attribute of the target entity identifier type. In case you want to
attribute of the target entity identifier type. In case you want to

insert a null, therefore stating that this record has no equivalent on the first
insert a null, therefore stating that this record has no equivalent on the first

entity, you can use the NullIdentifier() function. Let's look now into some of the
entity, you can use the NullIdentifier() function. Let's look now into some of the

usual patterns for data modeling using relationships. The first one is an
usual patterns for data modeling using relationships. The first one is an

extension entity, where its ID is actually of the type of another entity's
extension entity, where its ID is actually of the type of another entity's

identifier, this carries the concept that the second entity cannot live, cannot
identifier, this carries the concept that the second entity cannot live, cannot

exist, without the first entity having been created first.
exist, without the first entity having been created first.

You must therefore always have created an author, obtain his ID and use it
You must therefore always have created an author, obtain his ID and use it

explicitly in the biography that you're about to create in the database, before it
explicitly in the biography that you're about to create in the database, before it

gets inserted. This extension or one-to-one relationship is called so
gets inserted. This extension or one-to-one relationship is called so

because, each single biography belongs only to a single author and by view of
because, each single biography belongs only to a single author and by view of

the fact, that author ID is a
the fact, that author ID is a

primary key of biography, you cannot have two biographies with the same author ID
primary key of biography, you cannot have two biographies with the same author ID

therefore each author has at most one biography. It's easy to see that in
therefore each author has at most one biography. It's easy to see that in

concept, these two entities could actually be the same, they could actually
concept, these two entities could actually be the same, they could actually

be joined together into a single entity, but for some design reason we decided
be joined together into a single entity, but for some design reason we decided

not to do it explicitly and used instead the extension pattern. The second and most
not to do it explicitly and used instead the extension pattern. The second and most

common pattern is the master detail pattern. In this case the detail entity
common pattern is the master detail pattern. In this case the detail entity

references the master entity, in our example, a publisher publishes multiple
references the master entity, in our example, a publisher publishes multiple

books, so each book has got a publisher ID referencing who is the publisher for
books, so each book has got a publisher ID referencing who is the publisher for

that book. In your application, if you decided certain books may not have a
that book. In your application, if you decided certain books may not have a

publisher (they might be self-published for example), you might want to not
publisher (they might be self-published for example), you might want to not

enforce these as mandatory. Therefore you would be able to create books without
enforce these as mandatory. Therefore you would be able to create books without

setting a publisher, by leaving the publisher ID as a Null identifier. This
setting a publisher, by leaving the publisher ID as a Null identifier. This

master detail or one-too-many pattern is called so because each publisher may
master detail or one-too-many pattern is called so because each publisher may

have many books and in turn multiple books may belong to a single publisher.
have many books and in turn multiple books may belong to a single publisher.

The third pattern that we're going to talk about is the junction entity, it is
The third pattern that we're going to talk about is the junction entity, it is

implemented by having a third entity that correlates two entities. Since the
implemented by having a third entity that correlates two entities. Since the

book can have multiple authors and an author can, in turn, write multiple books
book can have multiple authors and an author can, in turn, write multiple books

it's easy to see that we cannot use a master-detail relationship, as we
it's easy to see that we cannot use a master-detail relationship, as we

described previously. So we use this junction entity BookAuthor, that
described previously. So we use this junction entity BookAuthor, that

references the author by its ID and references the book also by its ID.
references the author by its ID and references the book also by its ID.

This junction entity leverages and many-to-many relationship because each
This junction entity leverages and many-to-many relationship because each

author may have written multiple books and each single book may have multiple
author may have written multiple books and each single book may have multiple

authors. Now, how do you enforce the cohesion of your data model of the data
authors. Now, how do you enforce the cohesion of your data model of the data

in your database, how do you enforce referential integrity? In the OutSystems
in your database, how do you enforce referential integrity? In the OutSystems

platform you do so by specifying
platform you do so by specifying

the delete rule on a reference attribute. You need to set the delete rule in every
the delete rule on a reference attribute. You need to set the delete rule in every

single entity referenced attribute and it needs to be one of the following values:
single entity referenced attribute and it needs to be one of the following values:

it can be set to protect, which means that a record on the entity being
it can be set to protect, which means that a record on the entity being

referenced cannot be deleted as long as there are records in this entity that
referenced cannot be deleted as long as there are records in this entity that

are pointing at it. This is the default setting. It can also be set to delete
are pointing at it. This is the default setting. It can also be set to delete

which means that deleting a record on the entity, being referenced, will
which means that deleting a record on the entity, being referenced, will

automatically delete the records on the current entity that point at it. Finally
automatically delete the records on the current entity that point at it. Finally

there's the ignore option:
there's the ignore option:

this means that deleting a record on the entity being referenced will do
this means that deleting a record on the entity being referenced will do

nothing in the current entity. This does not guarantee, therefore, the referential
nothing in the current entity. This does not guarantee, therefore, the referential

integrity in this relation and it's a very, very uncommon pattern to use. In
integrity in this relation and it's a very, very uncommon pattern to use. In

the interest of performance, the OutSystems platform also allows you to create indexes
the interest of performance, the OutSystems platform also allows you to create indexes

over attributes in your entities. If you want to speed up fetches when filtering
over attributes in your entities. If you want to speed up fetches when filtering

over a known set of attributes, you should create an index over those attributes. It's
over a known set of attributes, you should create an index over those attributes. It's

important to note that entity reference attributes, already have automatically
important to note that entity reference attributes, already have automatically

indexes created on them.
indexes created on them.

It's worth noting that you can set an index to be unique, which allows the
It's worth noting that you can set an index to be unique, which allows the

database to enforce that no two records will ever have the same combined value
database to enforce that no two records will ever have the same combined value

over the attributes that make up that unique index. This is quite handy and
over the attributes that make up that unique index. This is quite handy and

among other things, it will allow you to simulate composite keys. Now you've been
among other things, it will allow you to simulate composite keys. Now you've been

doing all of this modeling and it's quite useful to actually have a visual
doing all of this modeling and it's quite useful to actually have a visual

representation of your model so you know where you're at, and this is where
representation of your model so you know where you're at, and this is where

the entity diagram comes in. Inside service studio you can create as many entity
the entity diagram comes in. Inside service studio you can create as many entity

diagrams as you want and by dragging and dropping your entities into it, you will
diagrams as you want and by dragging and dropping your entities into it, you will

be able to get the visual representation
be able to get the visual representation

of the data model of the relationships between the entities that you've dragged. In this
of the data model of the relationships between the entities that you've dragged. In this

screenshot we can see our book related data model and the relationships that
screenshot we can see our book related data model and the relationships that

all of the entities have with each other.
all of the entities have with each other.

Notice that the relationships between the entities are actually color-coded in
Notice that the relationships between the entities are actually color-coded in

the entity diagram: so in a strong gray you have those entity references where
the entity diagram: so in a strong gray you have those entity references where

the delete rule is protect, in red you have those entity references where the
the delete rule is protect, in red you have those entity references where the

delete rule is delete and finally, on the light gray you have ignore delete
delete rule is delete and finally, on the light gray you have ignore delete

rules and this is it for modeling data relationships with OutSystems. See you
rules and this is it for modeling data relationships with OutSystems. See you

guys on the next lesson!
guys on the next lesson!