Module 2 - Bussiness requirements to Data model

#

The process #

First we need to analise the requirements and create the data model. By analysing the requirements, we determine that the central data structure is the book. We create an entity for the book that has a title and a stock. The stock increases when a book instance is created and decreses when it is deleted.

That might look something like:

erDiagram
  book {
    string title
    int stock
    int key PK "id_book"
  }

Now, as we need to add some relationships, we see that the requirement says that each book has an author. There are two ways we can handle this: first, we can add a property author directly to the book entity. However, this approach would cause redundancy if we need to track multiple books by the same author. The better approach is to create a separate entity for authors and establish a relationship between the book and author.

erDiagram
    author {
        string name
        int key PK "id_author"
    }

     book {
        string title
        int stock
        int key PK "id_book"
        int id_author FK "id_author"
    }

    author one--many book : has

Books can belong to multiple categories (e.g., “Science” and “Fiction”), and categories can contain multiple books. This creates a many-to-many relationship. To manage this, we introduce a join table called book_category to link books and categories.

Note: The “uuid” field is automatically generated.

The book_instance entity represents individual copies of books available for borrowing. Each instance has an “in_stock” field to track availability after borrowings and returns. The “in_stock” field is of type int, with a value of 1 (true) when the instance is created and 0 (false) when the instance is borrowed. After the customer returns the copy, the “in_stock” field will revert to 1, indicating that the book is back in stock.

The book_history entity tracks the borrowing and returning of books. It includes:

• date: The date the book was borrowed/returned.
• customer: The customer who borrowed the book.
• tax: The penalty for late returns (if the return date exceeds 7 days from the borrow date).

The book_history_type entity categorizes the types of history records, with the name field having values like “Borrowed” or “Returned”.

The customer entity stores customer details, including a “total_tax” field, which tracks the total amount the customer owes for late book returns.

erDiagram

    customer {
        string name
        string phone
        string email
        decimal total_tax
        int key PK "id_customer"
    }

    author {
        string name
        int key PK "id_author"
    }

    book_category {
        int key PK "id_book_category"
        int id_category FK "id_category"
        int id_book FK "id_book"
    }

    category {
        string name
        string label
        string uuid
        int key PK "id_category"
    }

    book {
        string title
        int stock
        int key PK "id_book"
        int id_author FK "id_author"
    }

    book_history_type {
        string name
        string label
        string uuid
        int key PK "id_book_history_type"
    }

    book_history {
        datetime date
        decimal tax
        int key PK "id_book_history"
        int id_book_history_type FK "id_book_history_type"
        int id_book_instance FK "id_book_instance"
        int id_customer FK "id_customer"
    }

    book_instance {
        string barcode_number
        int in_stock
        int key PK "id_book_instance"
        int id_book FK "id_book"
    }

    book one--many book_category : belongs_to
    category one--many book_category: has
    author one--many book : has
    book_history many--one customer : involves
    book_instance one--many book_history: has
    book_history many--one book_history_type : classified_as
    book one--many book_instance : has

What will be presented #

In this lesson, we will guide you step by step through creating the library app’s entities, search views, edit views, menus, catalogs, event listeners, and entity actions. All these entities are essential for the app to function properly. It’s recommended to complete the lesson using the knowledge from previous lessons and the key content here and in upcoming modules. Note that we won’t cover all validations — creating them is optional if you choose to do so, but we will cover the most important aspects.

ONE ERP App Data Model #

The first step in designing a ONE ERP app is constructing the data model. The data model consists of entities, properties, and relationships between entities.

Step 1: Defining the entities, properties and relationships #

1. book #

• Properties:
  • title: string
  • stock: int
• Relationships:
  • book_book_category: one-to-many to “book_category” (linking book to category).
  • book_book_instance: one-to-many to “book_instance” (tracking instances of book).

2. author #

• Properties:
  • name: string
• Relationship:
  • author_book: one-to-many to “book” (1:n)

3. category #

• Properties:
  • name: string
  • label: string
  • uuid: string
• Relationship:
  • category_book_category: one-to-many to “book_category” (linking category to book)

4. book_category #

5. book_instance #

• Properties:
  • barcode_number: string
  • in_stock: int

6. book_history #

• Properties:
  • date: datetime
  • tax: decimal
  • book_history_book_instance: many-to-one to “book_instance” (n:1)
  • book_history_customer: many-to-one to “customer”
  • book_history_book_history_type: many-to-one to “book_history_type”

7. book_history_type #

• Properties:
  • name: string
  • label: string
  • uuid: string

8. customer #

• Properties:
  • name: string
  • phone: string
  • email: string
  • total_tax: decimal

Step 2: Creating the entities #

Now that the data model is defined, it’s time to create the entities. A customization set will be created for each entity in the data model. This set includes the entity along with its properties and relationships. For this example, we will create a single customization set called “book” that includes all the entities described in the previous step. However, if it’s easier, you can create a separate customization set for each entity.

To create a customization set, you will need to follow the steps presented in Lesson 1 the Products.

For example, the book entity has the following property:

and the book_instance entity would have:

Note: The “in_stock” field should be of type bool, but we will use int instead. Since the boolean type is unavailable, it will take values 0 (false) and 1 (true) as integers to manage borrowing and returning.

For more information on entities, check the Entities page in the Develop App module.

Step 3: Defining the relationships between the entities #

The relationships between entities are essential for their interaction. Here’s a breakdown of each relationship and how it aligns with business needs:

1. Book to Book_Category (One-to-Many): A book can belong to multiple categories through the Book_Category table, which helps organize the library and enables category-based searches.
2. Category to Book_Category (One-to-Many): A category can be associated with multiple books through the Book_Category table, helping staff keep books organized and easily accessible.
3. Author to Book (One-to-Many): An author can write multiple books, allowing tracking of authors and their works.
4. Book_Instance to Book_History (One-to-Many): Tracks the borrowing and returning history of each individual book instance, supporting record-keeping and penalty (tax) calculations.
5. Book_History to Customer (Many-to-One): Links each borrowing/return event to a specific customer, helping track borrowing behavior and tax assessments.
6. Book_History to Book_History_Type (Many-to-One): Classifies book history records (borrowed/returned) for transaction tracking and reporting.
7. Book to Book_Instance (One-to-Many): A book can have multiple instances, allowing the library to track individual copies of a book for borrowing and return.

Relationship Example #

Continue defining the remaining relationships to ensure the proper functionality of the app.

To create relationships for this table, follow the steps presented in Lesson 2 the Invoices. After the relationships have been created, publish the customization set. To publish an entity, see Publishing the entities from Lesson 1 the Products .

For more details, see the Documentation → Develop App → Customization Set.