Entity Relationship

Different ways to declare entity

1. In schema.cds file (Domain Model Entity or Persistent entity)

It is a Persistence entity - means data is stored to non-volatile storage (like SSDs, hard drives, or databases) so it survives system reboots and application closures.
It by default creates the DB Table.
It doesnot directly exposed as OData API.

db/schema.cds

namespace cap.application.db.schema;


entity Warehouse {
    key ID      : UUID;
        name    : String;
        owner   : String;
        address : String;
}

2. In service.cds file (Service Entity or Non-Projected Entity)

It is a non-projected service entity because it is defined directly inside the service and is not a projection on a database entity.
It is non-persisted and handled with custom logic in service.js file.
It doesnot create DB Table by default.
It is exposed as OData API.

srv/employee-service.cds

service EmployeeService {

    entity EmployeeSummary {
        key ID   : UUID;
            name : String;
            count: Integer;
    };

}

Association

An Association is a relationship between two entities that allows one entity to reference another entity.
In an Association, the relationship between the entities is weak, meaning records in both entities can exist independently.
It means the lifecycle of both entities is independent, and the child entity records remain even if the associated parent record is deleted.

Composition

A Composition is a strong parent-child relationship between two entities where the child belongs to the parent and is part of the parent's lifecycle.
In Composition, the relationship between the entities is strong, meaning records in both entities cannot exist independently.
It means the lifecycle of both entities is dependent, and the child entity records gets deleted if the associated parent record is deleted.

Deep Insertion

Deep insertion means creating a parent entity and its composed child entity/entities in a single request.

Different type of relations in capm

One-to-One Relation

A one-to-one relationship is a relationship between two entities where one record in the first entity is associated with only one record in the second entity, and vice versa.

We can define it in db schema as -

db/schema.cds

entity Employees {
    key ID           : UUID;
        name         : String;
        email        : String;

        profile      : Composition of one EmployeeProfiles on profile.employee = $self;

}

entity EmployeeProfiles {
    key ID          : UUID;
        phoneNumber : String;
        address     : String;
        dateOfBirth : Date;

        employee    : Association to one Employees;
}

Once entities are defined, we can project it in service file as -

srv/employee-service.cds

using { cap.application.db.schema } from '../db/schema' ;

service EmployeeService @(path : 'employee') {
    
    entity Employees as projection on schema.Employees;

    entity EmployeeProfiles as projection on schema.EmployeeProfiles;

}

And we can define the custom logic to perform deep insertion like-

srv/employee-service.js

import cds from '@sap/cds';

//  connect to the database
const { Employees } = cds.entities('cap.application.db.schema');

//  service implementation
const EmployeeService = async (srv) => {

    srv.on('CREATE', 'Employees', async (request) => {
        try {
            const { name, email, profile } = request.data;

            //  validation check
            if (!name || !email || !profile) {
                return request.reject(400, 'Name, Email, and Profile are required fields');
            }

            const employeeID = cds.utils.uuid();

            const newEmployeeEntry = {
                ID: employeeID,
                name: name,
                email: email,
                profile: {
                    ID: cds.utils.uuid(),
                    phoneNumber: profile.phoneNumber,
                    address: profile.address,
                    dateOfBirth: profile.dateOfBirth,
                    employee_ID: employeeID
                }
            }

            const transaction = await cds.tx(request);

            const newEmployee = await transaction.run(INSERT.into(Employees).entries(newEmployeeEntry));

            if(!newEmployee) {
                return request.reject(500, 'Failed to create employee entry');
            }

            return { 
                status: 201, data: { message: 'Employee entry created successfully', employeeID: employeeID } };


        }
        catch (error) {
            console.error('Error creating employee entry:', error);
            return request.reject(500, 'Internal server error');
        }
    });

}

export default EmployeeService;

We can test the defined endpoint like-

test-api/employee-service.http

### Create Employee
POST http://localhost:4004/odata/v4/employee/Employees
Content-Type: application/json

{
    "name": "Vikash Patel",
    "email": "vikash.patel@example.com",
    "profile": {
        "phoneNumber": "123-456-7890",
        "address": "123 Main St, City, State 12345",
        "dateOfBirth": "1990-01-01"
    }  
}

One-to-Many or Many-to-One Relation

A One-to-Many or Many-to-One relationship in SAP CAP is a relationship where one record of one entity can be associated with multiple records of another entity, while each record in the second entity is associated with only one record in the first entity.

For Example, many Employees will be part of One organization.

We can define it in db schema as -

db/schema.cds

namespace cap.application.db.schema;

entity Employees {
    key ID           : UUID;
        name         : String;
        email        : String;

        organization : Association to one Organizations;
}


entity Organizations {
    key ID        : String;
        name      : String;
        location  : String;

        employees : Composition of many Employees on employees.organization = $self;
}

Once entities are defined, we can project it in service file as -

srv/employee-service.cds

using { cap.application.db.schema } from '../db/schema' ;

service EmployeeService @(path : 'employee') {
    
    entity Employees as projection on schema.Employees;

    entity EmployeeProfiles as projection on schema.EmployeeProfiles;

}

And we can define the custom logic to perform deep insertion like-

srv/employee-service.js

srv.on('CREATE', 'Employees', async (request) => {
    try {
        const { name, email, organization } = request.data;

        //  validation check
        if (!name || !email || !organization) {
            return request.reject(400, 'Name, Email, and Organization are required fields');
        }

        const employeeID = cds.utils.uuid();

        const newEmployeeEntry = {
            ID: employeeID,
            name: name,
            email: email,
            organization: {
                ID : organization.ID
            }
        }

        const transaction = await cds.tx(request);

        const newEmployee = await transaction.run(INSERT.into(Employees).entries(newEmployeeEntry));

        if(!newEmployee) {
            return request.reject(500, 'Failed to create employee entry');
        }

        return { 
            status: 201, data: { message: 'Employee entry created successfully', employeeID: employeeID } };


    }
    catch (error) {
        console.error('Error creating employee entry:', error);
        return request.reject(500, 'Internal server error');
    }
});

We can test the defined endpoint like-

test-api/employee-service.http

### Create Employee
POST http://localhost:4004/odata/v4/employee/Employees
Content-Type: application/json

{
    "name": "Vikash Patel",
    "email": "vikash.patel@example.com",
    "organization": {
        "ID" : "ORG002"
    }  
}

Many-to-Many Relation

A Many-to-Many relationship in SAP CAP is a relationship where multiple records of one entity can be associated with multiple records of another entity.

SAP CAP does not provide direct support for many-to-many relationships between entities. Instead, such relationships are implemented using a third entity (also known as a junction or link entity).

We can define it in db schema as -

db/schema.cds

namespace cap.application.db.schema;

entity Students {
    key ID             : UUID;
        name           : String;
        email          : String;

        studentCourses : Composition of many StudentCourses on studentCourses.student = $self;
}

entity Courses {
    key ID             : UUID;
        name           : String;
        duration       : Integer;

        studentCourses : Composition of many StudentCourses on studentCourses.course = $self;
}

entity StudentCourses {
    key ID      : UUID;

        student : Association to one Students;
        course  : Association to one Courses;
}

Once entities are defined, we can project it in service file as -

srv/employee-service.cds

using {cap.application.db.schema} from '../db/schema' ;

service StudentService @(path : 'student') {
    entity Students as projection on schema.Students;
    entity Corses as projection on schema.Courses;

    entity StudentCourses as projection on schema.StudentCourses;
}

And we can define the custom logic to perform deep insertion like-

srv/employee-service.js

import cds from '@sap/cds';

const { Students, StudentCourses } = cds.entities('cap.application.db.schema');

const StudentService = async (srv) => {

    srv.on('CREATE', 'Students', async (request) => {
        try {
            const { name, email, studentCourses } = request.data;

            if (!name || !email || !studentCourses || !Array.isArray(studentCourses)) {
                return request.reject(400, 'Invalid request data');
            }

            const transaction = await cds.transaction(request);

            const studentId = cds.utils.uuid();

            const newStudentEntry = {
                ID: studentId,
                name: name,
                email: email
            }

            const newStudent = await transaction.run(
                INSERT.into(Students).entries(newStudentEntry)
            );

            if (!newStudent || newStudent.length === 0) {
                return request.reject(500, 'Failed to create student entry');
            }

            const studentCoursesEntries = [] ;

            studentCourses.forEach(element => {
                const studCourseObj = {
                    ID : cds.utils.uuid(),
                    student_ID : studentId,
                    course_ID : element.course.ID
                }
                studentCoursesEntries.push(studCourseObj);
            });

            console.log(studentCoursesEntries);

            const newStudentCourses = await transaction.run(
                INSERT.into(StudentCourses).entries(studentCoursesEntries)
            );

            if (!newStudentCourses || newStudentCourses.length === 0) {
                return request.reject(500, 'Failed to create student courses entries');
            }

            return {
                studentID: studentId            
            }
        }
        catch (error) {
            console.error('Error creating student entry:', error);
            return request.reject(500, 'Internal server error');
        }
    });

}

export default StudentService;

We can test the defined endpoint like-

test-api/employee-service.http

### Create Student
POST http://localhost:4004/odata/v4/student/Students
Content-Type: application/json

{
    "name": "John Doe",
    "email": "john.doe@example.com",
    "studentCourses": [
        {
            "course": {
                "ID": "950e8400-e29b-41d4-a716-446655440002"
            }
        },
        {
            "course": {
                "ID": "950e8400-e29b-41d4-a716-446655440004"
            }
        }
    ]
}

!!! Its Done !!!