# How to Define Entities and Relationships in the PicoLisp Database Now we know how we can save objects to a persistent data base file, let's see what other tools we have to make life easier for us. For this purpose, we can use the PicoLisp Entity-Relation Framework. ------------------------------- ### Some basic vocabulary First of all, let's get clear on the basic vocabulary. What is an entity, and what is a relation? In its most basic meaning, an *entity* is an object that **exists** (physically or logically). That's the only requirement. A *relationship* captures how entities are related to one another. Usually all objects in a PicoLisp database are of a direct or indirect subclass of ``+Entity``, and relationships are subclasses of the abstract class ``+relation``. The relation of an entity to other data can be described using the ``rel`` function. Entity-Relation is sometimes abbreviated as "E/R". ------------------------------- ### An example Let's say we want to describe a family model in our database. A family consits of **persons**. So let's define a class ``+Person`` that inherits directly from ``+Entity`` (like normally all database objects do). We create a PicoLisp file called ``family-dbmodel.l`` and define our ``+Person`` class. ``` (class +Person +Entity) ``` What kind of relations does a person have? First of all, a person has a **name**. In our model, the name should be required ( ``+Need``), and a name needs to be a string (``+String``). Also, we want to be able to search our database for that name as full-text (``+Idx``), and also find it if we mistyped due to phonetic similarities (``+Sn`` for [soundex algorithm](https://en.wikipedia.org/wiki/Soundex)). ``` (rel name (+Need +Sn +Idx +String)) ``` What else? A person probably has a **birthday** and possibly also a day of **death** (``+Date``), probably also a **job** (``+String``), and maybe some additional **info** that we didn't think of yet (``+String``). It would be nice if we could find our person by searching and filtering our database for jobs or birthday/death date (``+Ref`` for non-unique indexes). ``` (rel job (+Ref +String)) (rel birthday (+Ref +Date)) (rel deathday (+Ref +Date)) (rel info (+String)) ``` Also, we should define some subclasses to distinguish between **men** (``+Man``) and **women** (``+Woman``). Let's not consider any other gender constellations here because it would make it too complicated (like in real life). Men and women are inheriting the above defined relationships from ``+Person``: ``` (class +Man +Person) (class +Woman +Person) ``` ------------------------------- Now we come to the interesting part: The family relationship. In PicoLisp, we can define three main types of object-to-object relations: - ``+Link``: an uni-directional link to some other entity, - ``+Joint``: a bi-directional link to some other entity, - ``+Hook``: a reference to an entity holding object-local index trees. Any person can have a **partner** (we keep it simple - only one). This is clearly a bidirectional relationship, because the the entity's partner's partner is the entity itself. Every person can have a partner relationship. ``` (rel partner (+Joint) partner (+Person)) ``` Next, the parents-kids-relationship. Any person has exactly one **father**. That person needs to be a ``+Man``. On the father side, the entity appears at ``kid``. ``` (rel father (+Joint) kids (+Man)) ``` Analogously for the **mother**: ``` (rel mother (+Joint) kids (+Woman)) ``` ------------------------- What is the advantage of using ``+Joint`` relationship classes? For example, it can be useful if the relationship gets deleted from one side of the entity. Let's say, a person is getting a new ``partner``. Then automatically, the ``partner`` property of the *other* entity gets cleared (he/she is single). On the other hand, if the *new partner* of that entity already had a partner, then their relationship is cut off too. This is all maintained automatically. -------------------------- Now there is one last thing we need to define: We said that a person has a ``father``, who on the other side has a property ``kids``. A person can only have exactly one father, but an undefined number of kids. So let's define those as ``+List``: ``` (class +Man +Person) (rel kids (+List +Joint) father (+Person)) (class +Woman +Person) (rel kids (+List +Joint) mother (+Person)) ``` ----------------------------- This is all we need to define our data model. In the next post, we will see how we can use it. ----------------------------- *Obviously we didn't cover all relationships in this little example. You can find an overview over the most important relation classes in the [PicoLisp documentation](https://software-lab.de/doc/ref.html#dbase) in the chapter "Entities/Relations".* ------------------------------ # Sources https://software-lab.de/doc/ref.html#dbase [PicoLisp tutorial](https://software-lab.de/tut.tgz)