Designing DTOs that have foreign key relationships

Question

I am using Java + Spring framework for a web-application. I am not using any ORM tool. Instead I am trying to model the db relations as Java objects using simple DAO/DTO pattern. Whenever the DTO exactly corresponds to a single table in the database, it is very straight forward. But if there are tables that refer to other tables using foreign keys, I am not sure what is the best approach for this. Looked in Stackoverflow for similar answers but could not find one to my needs. I want to give a very specific example- Suppose there are two entities User and Group. I have a User DTO and Group DTO, each one having UserDao(JdbcUserDao) and GroupDao(JdbcGroupDao) respectively.

Now I have a relation in DB that connects the User and Group. One user can belong to multiple groups. The table name is User_Group_Association that has the following DB definition:

user_id | group_id

Here user_id is a foreign key referring to user table. Similarly group_id refers to group table. When I model this DTO in Java, should I do something like this:

public class UserGroupAssoc {
    private int userId;
    private int groupId;

    //Setters and getters follow
}

OR should it be like this:

public class UserGroupAssoc {
    private User user;
    private Group group;

    //Setters and getters follow
}

Particular UI use case: I want to display usernames and the corresponding group names they belong to. Something like this-

Name -> Group Names

Keshav -> Admin, EndUser, ReportAdmin
Kiran -> ReportAdmin
Pranav -> EndUser

In the first approach to DTO design, I will need to fetch the user details (names) and group details (names) again from the DB. In the second approach, I would need to fetch the User and Group objects when i am constructing the UserGroupAssoc object itself.

In probably a third approach I can design the UserGroupAssoc DTO as follows:

public class UserGroupAssoc {
    private String userName;
    private String groupName;
    private int userId;
    private int groupId;

    //Setters and getters follow
}

In this third approach, I join tables in SQL to obtain only the needed fields for the use-case and then model the DTO accordingly.

Which is the standard approach to achieve this scenario? Is joining tables alright in the DTO design? Some have opinions that one DTO should correspond to only ONE table and the associated objects should be aggregated in the app layer. That has overhead of doing multiple object fetches from DB right? Too confused about the right approach, sorry for such a long explanation!

Answer 1

Disclaimer: I'm not an ORM specialist...

... but in a classic many-to-many relations you don't need the third data model (UserGroupAssoc). The User class will contain a collection of Groups:

public class User {
   // ... user fields ...
   List<Group> groups;
   // ... getters/setters ...
}

If you need also the inverse relation (a group contains users) the Group class will look like this:

public class Group {
   // ... group fields ...
   List<User> users;
   // ... getters/setters ...
}

And again, the classic way of doing it is to use "domain objects" (your DTOs) in the collection (User and Group instead of userId and groupId).

You normally need a third domain object only if the association table (User_Group_Association) contains something else than user_id and group_id (maybe some authorization code that allowed a user to be added to the group, whatever):

user_id | group_id | auth_code

In this case the UserGroupAssoc class might have this structure:

public class UserGroupAssoc {
    private User user;
    private Group group;
    private String authorizationCode;

    // ... setters/getters ...
}

And the many-to-many relation between User and Group will transform to two many-to-one relations with this new domain object. Normally the domain objects are preferred to be used (User and Group instead of userId and groupId).

Which is the standard approach to achieve this scenario?

Well, if you were using an ORM framework it would have been the standard way the framework was doing it. But since you have a custom ORM solution, it's hard to say.

Is joining tables alright in the DTO design?

Why should the DTO design in the application layer be influenced by joining tables in the database? This is a case for the object-relational impedance mismatch and maybe also the law of leaky abstraction as you can't fully abstract the relational domain into an object domain keeping a 1:1 correspondence.

Some have opinions that one DTO should correspond to only ONE table and the associated objects should be aggregated in the app layer. That has overhead of doing multiple object fetches from DB right?

ORM has some limitations (see again the object-relational impedance mismatch for some issues you might run into) and it is hard to model your domain objects to accommodate the constraints of the relational database, or vice-versa. Reports are good example in this direction.

A report normally aggregates data from multiple tables. This is of course no problem for some SQL joins, but how are you going to map the result to DTOs? As you said it yourself...

Whenever the DTO exactly corresponds to a single table in the database, it is very straight forward

... but the report result won't map to a single table, it will have to map to fragments from more tables.

Depending on what your needs are in the application, you might end up with some weird looking classes or with awkward looking SQLs. You might end up running more queries than needed to obtain the proper object models and associations between them; or you might have more similar DTOs just to limit the number of trips to the database and gain better performance.

So what I'm trying to say (again the disclaimer that I'm not an ORM specialist) is that not all applications are a good candidate for ORM; but if you consider going ahead with it maybe look into how Hibernate/JPA is addressing the issues or even go ahead and use them instead.