How architecturally to work with a DB if hierarchical structures have the same fields and the field of the lower link has a priority?

Question

Database design

There are moments in the project that confuse me. For example, the weight of a product is indicated in the product and in the product category. If the weight is not specified in the product, then we take the weight from the category (group) of the product. A product group can also have parent groups. And if the weight in the group is not specified, then it must be taken from the parent group.

So that's all right. If only the database to use. but if at the code level you write a function that gets its weight by the id of the product and takes it in its weight field, and if it is not specified, then it takes it in its category and if it is not specified there, then in the parent one.

What is the approach to take in order to do all this?

Is it necessary to implement everything through queries in order to get the weight value or write a function on the side of the database server that would give a ready-made result?

Or one more example. "goods prohibited in country X". it is necessary that when adding a product, if it is given a product category, so that the sign of belonging to country X is affixed to it. And if its category is assigned a new attribute, so that the product is affixed to all products included there. And if it is affixed to the parent category, then so that all products would be displayed. And if at least one product is given a different sign, so that it is canceled for all higher categories.
same means that the field will be in all tables. And you can do without it. But keep in mind that there may be special cases. That, regardless of the hierarchy, an element may have its own special priorities and it will become the owner of a feature that the higher ones do not have.

How to design in this case? Lay fields with the same name in hierarchically-related entities?

Or is there a ready-made patern?

Answer 1

For each such problem, a system solution can be made according to the algorithm for reading and writing data. In your case, these will be function algorithms that return the desired values ​​based on the available data and process their changes. And the situation is closed by good documentation for these algorithms, available to developers. For good, it is necessary to prohibit direct access to data and make algorithmic getters and setters in order to exclude the situation when someone past the algorithms reads the data for the algorithms.