Object Oriented Design and Relational Databases: Where to place behavior that pertains to more than one tables?

Question

I have been trying to learn Object Oriented Design, but I find it very difficult to model objects that access a relational database and do JOINs.

For example, I am building a REST API, it has User, Admin and Event entities.
Admins can issue Users registration for Events.

This is the database schema:

User:
   id
   name

Event:
   id
   name

Admin:
   id
   name

UserEventRegistration:
   user_id
   event_id
   issued_admin_id

I have an endpoint /participant/registered_events which gives info of all events registered by a user.

class EventInfo {
   public int id;
   public String name;
   public int issuedAdminId;
   public String issuedAdminName;
}

EventInfo[] getRegisteredEvents(userid, participantid) {
   Result result = executeSql(
        "SELECT Event.id, Event.name," +
                " Admin.id as admin_id, Admin.name as admin_name" +
        " FROM UserEventRegistration " + 
        " JOIN Event ON event_id = Event.id " + 
        " JOIN Admin ON admin_id = Admin.id"
   );
   // convert result into EventInfo objects
   return eventsInfo;
}

The above code is not properly object oriented, but I don't know which class to place the code. If I put it in class RegistrationManager, now RegistrationManager is strongly coupled to the structure of Admin table and Event table.

Where is the appropriate place to put this JOIN query?

Edit (04 Oct 2019): Made the example a bit more clearer and complete. Incorporated suggestions from answers to narrow down the scope of the question.

Answer 1

Very often, when you have a behavior where you cannot decide which of two objects should have it, that is because either

• the two objects should actually be one object or
• there is a third object missing.

A great example is the classic "Bank account" that is so often used as an introduction to OO. In the typical example, balance is data and deposit is an operation. This leads to all sorts of problems, but the one we are focusing on here is: where do you put the transfer method? If a transfers money to b, should it be a.transfer(b, amount) or b.transfer(a, amount)? Why would a even know about b? Why would b even know about a? Why would either of the two even know how to transfer money?

However, that is actually not how banking works in real-life and is also not how banking systems are typically designed.

In the real-world, deposit is actually data (it is a transaction slip) and balance is an operation (summing up all transaction slips for one account). This is the way banking was done for hundreds of years, and it is actually also how banking systems are written.

This has some advantages for concurrency (now, both Accounts and TransactionSlips are immutable, and balance is a pure function). But, it also solves our conundrum above: neither a nor b know how to transfer money, the bank knows that. transfer is now actually new TransactionSlip(a, b, amount).

Back to your specific example: it looks like what you are missing is at least one object, possibly even two:

• Registration: Encapsulates the fact of a user being registered for an event
• Registry: contains all Registrations

Answer 2

One answer has already suggested that there is a third object required. I totally agree, and would introduce a Booking (or BookingManager) class that knows how to link (and delink) Users to Events. This class would support your registerUserForEvent(String userid, String eventid) and getRegisteredEventsForUser(String userid) methods.

Putting the booking logic in either the User or Event class breaks the rule that a class should do one thing and do it well. Users do not need to know about events, and events do not need to know about users.

I would not get too hung up on what database joins are required. Modern databases are very good at doing joins and worrying about them at design time is often regarded as premature optimisation.

Answer 3

If I put it in User class, it will have to know about how to check an event with given String eventId exists in the DB.

Objects are not tables, nor data. One object can very well know stuff from different tables, or join underlying tables together.

You did not state any additional behavior that needs to be supported, but based on what we know Event could be an interface and everything could be implemented in the User object. This way there is no violation of encapsulation.

Answer 4

There is the wonderful world of Inheritance that allows you to create a tree refinement for your classes. From top level own to fully detailed, with each layer inheriting the parent layer. Another option is Aspect Oriented Programming, which uses cross cutting annotations to adorn functions and attributes with behaviour orthogonal to the class function, things like logging and security are good examples.