Designing DTOs Related to a Foreign Key

Question

I am using Java + Spring framework for web application. I do not use the ORM tool. Instead, I am trying to model db relationships as Java objects using a simple DAO / DTO pattern. Whenever a DTO exactly matches a single table in a database, it is very simple. But if there are tables that reference other tables using foreign keys, I'm not sure what the best approach is for this. Looked at Stackoverflow for similar answers, but could not find it for my needs. I want to give a very concrete example. Suppose that there are two User and Group objects. I have a user DTO and a DTO group, each of which has UserDao (JdbcUserDao) and GroupDao (JdbcGroupDao) respectively.

Now I have a relationship in the database that connects the user and the group. One user can belong to several groups. The name of the table is User_Group_Association, which has the following DB definition:

user_id | group_id

Here user_id is the foreign key related to the user table. Similarly, group_id refers to the 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 be like this:

 public class UserGroupAssoc { private User user; private Group group; //Setters and getters follow } 

A specific example of using the user interface: I want to display the usernames and names of the corresponding groups to which they belong. Something like that -

Name → Group Names

Keshav → Admin, EndUser, ReportAdmin
Kieran → ReportAdmin
Pranav → EndUser

In the first approach to DTO design, I will need to get user data (names) and group data (names) from the database again. In the second approach, I will need to retrieve the User and Group objects when I create the UserGroupAssoc object myself.

Probably the third approach I can develop is 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 the tables in SQL to get only the required fields for the use case, and then model the DTO accordingly.

What is the standard approach to achieve this scenario? Do tables join in a DTO design? Some people have the opinion that one DTO should correspond to ONE table only, and related objects should be aggregated at the application level. Does this have the overhead of performing multiple sample objects from the database? Too confused about the right approach, sorry for such a long explanation!

Answer 1

Disclaimer: I am not an OPM specialist ...

... but in the classic many-to-many relationship, you don't need a third data model ( UserGroupAssoc ). The User class will contain the Group s collection:

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

If you also need an inverse relationship (the group contains users), the Group class will look like this:

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

Again, the classic way to do this is to use the "domain objects" (your DTOs) in the collection ( User and Group instead of userId and groupId ).

Usually you need a third domain object only if the association table ( User_Group_Association ) contains something else besides user_id and group_id (maybe some kind of authorization code that allows you to add the user to the group, whatever):

 user_id | group_id | auth_code 

In this case, the UserGroupAssoc class may have the following structure:

 public class UserGroupAssoc { private User user; private Group group; private String authorizationCode; // ... setters/getters ... } 

And the many-to-many relationship between User and Group translates into two multi-valued relationships with this new domain object. It is usually preferable to use domain objects ( User and Group instead of userId and groupId ).

What is the standard approach to achieve this scenario?

Well, if you were to use the ORM framework, that would be the standard way the infrastructure did it. But , since you have the usual ORM solution, it's hard to say.

Compatible tables in DTO design?

Why should the join of tables in the database influence the design of the DTO at the application level? This refers to the mismatch of the object-relational impedance and, possibly, also to the law of inaccurate abstraction , since you cannot completely abstract the relational domain into the subject domain, while maintaining a 1: 1 correspondence.

Some people have the opinion that one DTO should correspond to ONE table only, and related objects should be aggregated at the application level. Does this have the overhead of performing multiple sample objects from the database?

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

A report usually combines data from several tables. This, of course, is not a problem for some SQL joins, but how are you going to display the result in DTO? As you said yourself ...

Whenever a DTO exactly matches a single table in a database, it is very simple

... but the report result will not be displayed in one table, it will have to display fragments from other tables.

Depending on your application needs, you may encounter some strange class types or uncomfortable looking SQL. You can run more queries than you need to get the corresponding object models and associations between them; or you may have more similar DTOs to limit the number of trips to the database and improve 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 think about proceeding, perhaps see how Hibernate / JPA solves problems or even goes ahead and uses them instead.