microservices best practice - 3 level data structure

I have a three-level data structure

level_1
ID, NAME

level_2
ID, NAME, ID_LEVEL1

level_3
ID, NAME, ID_LEVEL2

The idea is to create a single microservice that manages the CRUDs on the three tables.

But what is the best solution?

Do you want to run an API that handles data entry in one go or manage tables with three different API groups?

I think entering the data in one go leads to the following problems:

• manage the CRUD transaction
• front end complexity

In your case, I recommand you to apply the composit design pattern, generally used to manage hierarchies.

And expose a single microservice to CRUD your levels.

In a single table LEVEL you'll have 3 columns :

• id (primary key)
• name
• parentId (nullable foreign key refers to level id, on delete cascade, that's means you will )

CREATE TABLE `level` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(500) NULL,
  `parentId` int unsigned NULL
);

ALTER TABLE `level`
ADD FOREIGN KEY (`parentId`) REFERENCES `level` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

The java class will have :

• The root level has a parentId null.
• The leaf level has no child.
• The level number is found using recursive method, starting at position 1.

import javax.persistence.*;
import javax.validation.constraints.Size;
import java.util.Set;


@Entity
@Table(name = "level")
public class Level
{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private int id;

    @Column
    @Size(max = 500)
    private String name;

    @ManyToOne()
    @JoinColumn(name = "parentId")
    private Level parent;

    @OneToMany(mappedBy="parent",cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    private Set<Level> children;


    public boolean isLeaf() {
        return (children == null || children.size() == 0);
    }
   
    public boolean isRoot() {
        return (parent == null);
    }
  
    public int getLevelNumber() {
        return getLevelNumber(1);
    }

    private int getLevelNumber(int currentLevel) {
        return parent == null ? currentLevel : parent.getLevelNumber(currentLevel+1) ;
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public Level getParent()
    {
        return parent;
    }

    public void setParent(Level parent)
    {
        this.parent = parent;
    }

    public Set<Level> getChildren()
    {
        return children;
    }

    public void setChildren(Set<Level> children)
    {
        this.children = children;
    }
}

In my point of you, this is the best and optimized solution.

And then I will have a single repository

public interface LevelRepository extends JpaRepository<Level, Integer>
{

}

If you really need to distinct levels in your database by a discriminator value, then you can add a discriminator values and manage multiple java objects. It will give you more visibility in your java project structure, but will really complexify your code and the transaction management.