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
please do so before proceeding.
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
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
books and authors and publishers to describe how these relationships can be
obtained in the OutSystems platform. Before we go into the specifics of
OutSystems
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
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
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
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
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
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
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
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
text. These attributes are always mandatory and in the case that you opt
for an integer based
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
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
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
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
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
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
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
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
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
exist, without the first entity having been created first.
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
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
the fact, that author ID is a
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
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
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
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
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
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
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.
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
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
described previously. So we use this junction entity BookAuthor, that
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
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
in your database, how do you enforce referential integrity? In the OutSystems
platform you do so by specifying
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:
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
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
automatically delete the records on the current entity that point at it. Finally
there's the ignore option:
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
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
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
important to note that entity reference attributes, already have automatically
indexes created on them.
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
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
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
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
be able to get the visual representation
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
all of the entities have with each other.
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 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
rules and this is it for modeling data relationships with OutSystems. See you
guys on the next lesson!