How to manipulate database columns in order to get the quantity of items using Hibernate?

Hibernate ORM
1

So my question is: how to manipulate database columns in order to get the quantity of items using Hibernate. I am using NetBeans IDE with Hibernate 5.4 and an Apache Derby database. I’ve never used Hibernate and I got a little-bit lost.

Here you can see the entity relationship diagram of my database’s tables

image

Here is the UI of the Java inventory application I want to create: https://i.stack.imgur.com/R04OK.png

My goal is to calculate the quantities of the items, for this I thought that I would have an In_Our_Out column, which could be either -1 (outgoing shipment) or 1 (incoming shipment). In the transactions table I would have** a Flow column, which would be the amount that is actually received or transferred. So I would have to first multiply the Flow values with either 1 or -1 which would be based on the connection between the Advicenote_IDs, am I correct? Then I would have to add the calculated negative and positive values according to the Item_ID s to get the quantity of each type of items.

So I am lost because I don’t know how to breakdown this process and how to make the necessary associations with the tables. Should I indicate the relationship between the tables in their entity classes using annotations (creating a transient column could be a solution?) or should I use join statements in this case? Or maybe the best would be to revamp the whole structure of the tables to store the quantities? How can I add and multiply the table columns with each other? I would be super grateful for any help!

2

This question is not related to Hibernate, so it’s better to ask it on the DBA StackOverflow channel since it’s about database modelling.

Also, the database schema design is derived from application requirements, hence it is specific to the project and so there is no general answer to your question.

3

Thank you for pointing me in the right direction!