Grouping and changing data structure - Mutable tasks with database data

Question

Writing some services on a system I'm working on has raised some questions as to how to provide the data formatted for the client (views or mobile app). Basically, my doubt is born whenever I need to provide grouped data or data that needs to be changed after the query.

I will use as an example a Has Many Through relationship:

countries
    id - integer
    name - string

users
    id - integer
    country_id - integer
    name - string

posts
    id - integer
    user_id - integer
    title - string

I'm using the basic example that the Laravel framework uses. But my structure is a bit more complex. Anyway, I'll try to put together a scenario based on this structure.

Let's assume that, by business rule, whenever I need to list posts, they will need to be grouped by countries. Therefore, I believe that because of being a business rule requirement, whenever my clients request posts, the posts service must provide the grouped data.

In the case above it is very simple, in the real case I have to additionally group, change some information of the fields of each country record and their respective posts. And at this point, I always wonder about the 'best' way to accomplish this data formatting.

• I have worked on systems that the fields that needed to be changed, were already solved through a view in the database that made the necessary relationships and already provided everything ready. Letting the programming solve the data grouping and possible changes of structures.
• In other systems, we used a powerful ORM that could do great inner joins while maintaining performance (which is not the case for all ORMs).
• And in other cases, due to the ORM-generated slowness of the framework we used, we made several small queries and then grouped and altered the data programmatically in repetitive structures.

First question: is there a RIGHT way to do this kind of work?

Second question: If it is a CASE question (which I believe it to be), if in any project I create several different ways to group data, will I be creating bad code?

Third question: if by chance I decide to do the data groupings programmatically, make a query in the database and then change the state of that result in the code, would it be a bad practice?

Sometimes I think that views in the database are always the best solutions. But in my real case, there are almost 120 tables. And I already have a lot of code that deals with large relationship structures programmatically from those tables. I think starting to write views now would create a mess from where the business rule is located.

What do you think about all these cases?

Thanks advance!

Answer 1

So the business requirement is for example to display all the posts grouped under country headings.

I would do either:

• 1 select all countries with posts
• 2 select all users with posts sorted by country
• 3 select all posts sorted by user and country

the build the display in code. due to the sorting only one loop through should be required.

OR

• 1 select all countries with posts
• 2 loop through, selecting all posts for each country in turn

This requires more db calls but avoids returning the user data.

These are not the most efficient queries for the specific task. But what I'm trying to avoid with these approaches is

• Creating new 'made up' objects by adding extra fields from other tables. ie countryId to post.

This is a dangerous road to go down because every UI design call will mean creating new 'postWithX' objects or adding additional fields untill you post object contains all the data in the world.

• Creating overly specific queries which are only useful for a single task.

I want to be able to reuse the same GetPostsByX, GetUserByY calls across many UIs not be designing new SQL queries just because the font changes. Try to avoid GetCountriesByUserLastNameSortedByDoB if possible.

• Creating complex objects which will include more information than required and will be slow to construct from many tables. ie Country.Users[0].Posts

That kind of thing is going to make the country dropdown selector on some other UI very slow to populate. You will end up creating 'Lite' versions of all your objects

In general I think its best to try and keep you objects the same same as they are in the DB

• Don't add or remove fields or relationships,
• Don't build complex objects with children of children of children. Just include the linking parent id as in the table