Core/DB Layer Question - One/Many to Many Relationships

[LeviMatus]

I've been following this project as a template awhile. I've liked the change from business/data/store to business/core/<entity>/db and how that clearly places the Access layer API beneath the Core layer.. One thing I've been stumped and can't seem to settle on is how to handle one-to-many or many-to-many relationships with this design.

Assume that we have a third type in this project, Order. A User can have many Orders and an Order can have many Products. For simplicity, lets just assume that an Order has an array of Product IDs and a FK relationship to a User (I only want to introduce one new type in my hypothetical example). When performing operations such as querying for the order history of a user, it seems to me that the line of where certain responsibilities belong gets blurry.

Scenario: As a user, I want to see my order history with order details and product details for each product in my historical orders.

The user sends a request to the API (lets just assume an endpoint exists to fetch orders for a user given a user's ID). The API handler dispatches a request to the User Core. From there I start having different options I tend to bounce between, not ever entirely happy with any of them:

1. User Core needs to negotiate between the Order and Product Cores to chain some queries together. Right now this sits the best with me for ease of use. That said, I don't like it because something about importing lateral cores seems like a breaking of responsibility and a smell. It doesn't follow the easy to comprehend structure of the project as it is laid out currently.

2. User Core needs to have SQL queries for Order and Product tables or hold a Store type for those entities. I feel like this is just point number one but with extra imports to get access to relevant data models.

3. usergrp Handler needs to hold Core types for User, Order, and Product and negotiate between the three. This solves the issue about lateral imports mentioned in point 1 (and by extension point 2), but now a new responsibility is being given to the Handler that I don't believe is in the scope of the Handler groups.

4. A larger Core type is needed to represent the relationship between the three and negotiate between them. In this case the request isn't handled by the User Core directly from the Handler, but by this larger aggregate Core. This I could be convinced of, but I think it begins to make the code a bit more complex than this project tries to be, and I'm aiming for something simple.

I find these all to be fairly lackluster. I'm hoping that I'm missing something more obvious. Is there an approach following this project's design philosophies that anyone has found and has been working well for them?

Any thoughts or opinions would be appreciated.

Edit: I'm currently using stored procedures to get around this. I like having in-line SQL where I can, but so far I think this has been the cleanest way of handling these sort of scenarios. I'm just wary of hiding business logic in SPs, but that's a personal preference.

[ardan-bkennedy]

In these scenarios my teams moved to option 4. I call this a "higher-level" core business package. It contains models that are a hybrid of the "lower-level" core packages it's using and APIs that perform the multi-step business logic.

I always wanted to put these in a separate layer under business, but couldn't find the right name. Plus adding another layer seemed too much to me.

I've ask @pavelaborilov-bnet to provide thoughts as well. He and I refactored these changes and have built these high-level core packages.

I think package creation/isolation for new functionality/models is always a good first step to prototype.

[LeviMatus]

I appreciate your feedback. I think I agree with the idea of conceptualizing them as higher vs lower core packages because they are essentially delegating responsibilities in a certain order.

Definitely interested to hear his thoughts. In the meantime I'm going to try experimenting with that option to see if I can find where my pain point was and if it can be rectified.

[cip8]

Hey Levi 👋

To handle these types of relationships I added a new layer under business: each entity is now represented by a Block. I try to keep these Blocks as pure as possible, containing only code related to that entity.

Blocks are orchestrated by the old Core layer, which I renamed to Anima (from Latin, meaning sprit / soul, that which combines blocks and "animates / gives life" to them). This way they sit on top of each other alphabetically too.

So now the Anima layer can include multiple Blocks.

Handlers delegate to Anima layers, but the Anima is self-sustained (contains authorization and everything else needed) and can be called from other parts of the app too.

[paborilov]

Hey! I'm glad that you've asked these questions, cause we were foreseeing that problem from the beginning. I will try to comment on all the options that you pointed out and will show you the more significant issue that can't be solved with any of those options :)

1. as you mentioned correctly, that is not a good option because it breaks the responsibility rule. To check myself, I always ask a question - "do User need to know about existing of product and order or it can exist without knowing that?" in this case, User is an entirely separate entity that should know nothing about product and order. First, we are adding user entity without any dependencies, and only then we are adding product entity and at last order that have to know about user and product. Order core can have a method that can list all orders for the specific user; that's fine. It can be just a Query function with a filter by a list of userIDs.

2. we put db package under specific core to show that only this core should have access to it; no other cores should access others db packages. And especially order/db packages should not join/query other entities like product or user. We made it so because if you need to change something db specific to product, for example, changes in the code will be limited to the products db package, and you won't need to search if any other packages join/query product table.

3. I wouldn't put this under userhandler cause this endpoint return list of orders; it makes more sense to put it under orderhandler. If you have Query method that I mentioned in the 1 point, all you need to do in the handler is to query for products by productIDs that you get from order.Query method. But that approach has other limitations that I will mention later.

4. that's just moving logic from orderhandler from point 3 to the new core, which makes sense only if other cores use that method.

5. putting logic to stored procedure is an easy workaround for that problem, but it breaks the responsibility rule, mixes db access, and put business logic into db :)

Now about the limitation that I mentioned and the bigger problem:

order.Query method with filter by UserIDs will be paginated, so it makes it harder to get the list of products, cause you need to collect all productIDs for all orders that returned(that can be a huge slice), then you need to query products with filter by productIDs, which in their turn should also be paginated :) and map products to the corresponding orders.

1. orders.Query(usersIDs) -> []Order{}
2. go through all orders and collect productIDs into set productIDs
3. the set can be huge, so probably we need to split it into batches
4. product.Query(productIDsBatch1) - probably also paginated; we need to go through all pages and collect products into slice []Product{}
5. repeat 4 for all productIDs batches
6. map []Order{} with []Product{}

We need to do that for all Order pages.

and here we come to limitation: probably the next feature that you will want to implement -

Scenario: As a user, I want to see my orders with order details and product details where the product name starts with something.

And that's it, now we need also to filter by product name, but if we do that, we will break our paging on step 4.

I'm hoping that I'm missing something more obvious. and the worst part about that - there is no obvious solution for that :)

but the good part is that it's a known problem and there is a solution for that; you can read about that here - https://microservices.io/patterns/data/cqrs.html, it states from the beginning

Problem: How to implement a query that retrieves data from multiple services in a microservice architecture?

the idea is to have a separate core with own db layer and table underneath with aggregated data, where you have all you need for filtering(product names, order detail, user info, etc), but to have this aggregated table in sync with others you have to rely on events and maybe event sourcing(https://microservices.io/patterns/data/event-sourcing.html), but that is an entirely different story :)

[ardan-bkennedy]

I'm closing this now. Hopefully this has helped everyone who has seen it.

[LeviMatus]

Thanks! Much appreciated!