How to create relationships in entities correctly

Question

The entity describing the machine

@Entity
@Table(name = "machine")
public class Machine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;      
    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "machine_node", joinColumns = @JoinColumn(name = "machine_id"), inverseJoinColumns = @JoinColumn(name = "node_id"))
    private List<NodeMachine> nodeMachines = new ArrayList<>();
}

The entity describing the part/node

@Entity
@Table(name = "node_machine")
public class NodeMachine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
}

Several parts are installed on each machine (nodeMachines list), and each part can be installed on different machines, so ManyToMany was added.

For example, a wheel can be mounted on a motorcycle or a car. A motorcycle can have two wheels, and a car can have four.

I will describe in more detail what is in the tables. I must say right away that the example is not very successful, just for understanding. In the Machine table we have 100 M motorcycles (1-100) and 100 C cars (1-100). And there is only one entry in the NodeMachine table - the K1 wheel, which is suitable for all one hundred motorcycles and for all one hundred cars. From this, there is no way to determine how many wheels each motorcycle and each car should have. Therefore, I believe that there should be a third table where the number of wheels is indicated for each car and motorcycles. And I think it's too redundant to keep 200 records of wheels for motorcycles and 400 records for cars in the table.

Each part is installed on a specific machine in a certain number. I want to get the number of nodes installed in a particular machine by knowing the node and machine name. To do this, you will have to create another count_machine_node table with the fields

- machine_id
- node_id
- count

I understand that you will have to create a new entity.

@Entity
@Table(name = "node_machine")
public class CountNodeMachine {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long machine_id;
    private Long node_id;
    private Integer count;
}

But what connections do you need to register in these entities?

How to link these three tables correctly?

And do I need to create a CountNodeMachine entity?

Answer 1

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

Answer 2

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)

Answer 3

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();
}

Answer 4

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.

Answer 5

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.