How to Define Entities and Relationships in the PicoLisp Database
Tags: picolisp, lisp, webdev, database
Published: 2022-03-15 10:26:52

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)