How to create relationships in entities correctly

Please comment if this serves the purpose.

Machine entity:

@Entity
@Table(name = "machine")
public class Machine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy="machineId")
    List<MachinePartCount> count;

Part entity

@Entity
@Table(name = "part")
public class Part {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy="partId")
    List<MachinePartCount> count;

Relation entity

@Entity
@Table(name="machine_part")
@IdClass(MachinePartCountPK.class)
public class MachinePartCount {
    
    
    @Id
    private Integer machineId;
    
    @Id
    private Integer partId;
        
    private Integer count;
    

Composite Key

@Embeddable
public class MachinePartCountPK implements Serializable{

    private static final long serialVersionUID = 1L;

    private Integer machineId;
    
    private Integer partId;

output:

    **machineService.findAll()**
    getAllMachines: [Machine [id=1, name=m1, count=[MachinePartCount [machineId=1, partId=1, count=2]]], Machine [id=2, name=m2, count=[MachinePartCount [machineId=2, partId=102, count=4]]]]
    
    **partService.findAll()**
    getAllParts: [Part [id=1, name=p100, count=[MachinePartCount [machineId=1, partId=1, count=2]]], Part [id=102, name=p10, count=[MachinePartCount [machineId=2, partId=102, count=4]]]]

    **machinePartCountService.findAll()**
    getmachinePartEntries: [MachinePartCount [machineId=1, partId=1, count=2], MachinePartCount [machineId=2, partId=102, count=4]]

Data in tables: machine table: machine part table: part machine_part table: machine_part

I always suggest to start development from the Java side. So, it might be something like

@Entity
@Table(name = "machine")
public class Machine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    // Список узлов машины
    @ElementCollection
    private Map<NodeMachine, Integer> nodeMachines = new HashMap<>();
}

Also, check my Hibernate articles (Russian and English versions)

You don't need the third entity, as you can do the sql query, or select machine by name, filter it's nodeMachines list and count the result.

For example, using the repository, like here you can select machine by id, or a list of machines by name, and then just filter it's(their's) node lists by name and count.

import java.util.List;

import org.springframework.data.repository.CrudRepository;

public interface MachineRepository extends CrudRepository<Machine, Long> {   
  List<Machine> findByName(String lastName);
}

And somewhere in your code:

@Autowired
private MachineRepository machineRepository;

public int countNodesInMachine(String machineName, String nodeName) {
    return machineRepository.findByName(machineName).stream()
        .flatMap(machine -> machine.getNodeMachines().stream())
        .filter(node -> node.getName().equals(nodeName))
        .count();
}

To do this, you can create a counter in the NodeMachine entity. This is economical in terms of occupied space in the database, but not true in terms of object orientation. Using the CountNodeMachine entity is not economical at all because a table is wasted to hold a number. I think you need a wrapper class that has NodeMachine and its counts. and use List of this to Machine Entity.

If I get from the above example, You can do is create a third entity namely as Categories.

As you have a machine specific category, like Motorcycles or Car. They have a specific set of wheels.

You can map that in this third table and create relation with the Machine.

`Categores

id| category| wheels| othernodes

1 | Car | 4 | 1

2 | Bike | 2 | 3`

and so on, you just will have to find the category and node from the machine table and get the count easily, also you can add new nodes for the same.

The only downside is that you machine will be specific to Category.