A Graceful Approach to Multi‑Table Queries: Embrace Aggregation, Avoid Stitching

Redundant vs Normalized Storage

Redundant storage improves query speed by eliminating joins, reduces I/O, and simplifies application logic, but it can cause data inconsistency, higher storage cost, and harder maintenance as data grows.

Normalized storage ensures data consistency, saves space, and supports integrity constraints, yet it may degrade query performance, increase I/O, and require more complex SQL for multi‑table joins.

Balancing Design Choices

In practice, strict normalization can hurt performance, so developers often apply selective denormalization—introducing controlled redundancy to meet query speed requirements.

Why Multi‑Table Aggregation Is Needed

When data is split across tables, a single‑table query cannot satisfy business needs. Traditional solutions use SQL JOINs, which increase database load and make SQL maintenance difficult as the codebase grows.

In‑Memory Aggregation (“fill” Pattern)

Instead of writing complex joins, the article proposes moving the aggregation to the application layer using a repository implementation that conditionally enriches the primary result set based on boolean flags in the query object.

Add a Boolean fillXxx field to the query DTO to control aggregation.

Implement fill(Query query, List<Model> models) in XxxRepositoryImpl; the method checks the flags and calls specific private helpers to pull related data.

Concrete Example: AppointmentOrder Aggregation

@Repository
public class AppointmentOrderRepositoryImpl extends BaseRepositoryImpl<AppointmentOrderMapper, AppointmentOrder, AppointmentOrderPO, AppointmentOrderQuery> implements AppointmentOrderRepository {
    @Override
    public void fill(AppointmentOrderQuery query, List<AppointmentOrder> appointmentOrders) {
        Map<String, AppointmentOrder> orderId2appointment = appointmentOrders.stream()
            .collect(Collectors.toMap(AppointmentOrder::getOrderId, o -> o));
        if (query.getFillAppointment()) {
            this.fillAppointment(appointmentOrders);
        }
        if (query.getFillAppointmentRecords()) {
            this.fillAppointmentRecords(orderId2appointment);
        }
        if (query.getFillAppointmentComment()) {
            this.fillAppointmentComments(appointmentOrders);
        }
    }

    private void fillAppointment(List<AppointmentOrder> appointmentOrders) {
        Set<String> appointIds = appointmentOrders.stream()
            .map(AppointmentOrder::getAppointId).collect(Collectors.toSet());
        List<Appointment> appointments = AppointmentQuery.builder().idIn(appointIds).build().list();
        if (CollKit.isEmpty(appointments)) return;
        Map<String, Appointment> map = appointments.stream()
            .collect(Collectors.toMap(Appointment::getId, o -> o));
        for (AppointmentOrder order : appointmentOrders) {
            Appointment a = map.get(order.getAppointId());
            if (a != null) {
                order.setAppointment(a);
                order.setAppointName(a.getName());
            }
        }
    }

    private void fillAppointmentRecords(Map<String, AppointmentOrder> orderId2appointment) {
        List<AppointmentRecord> records = AppointmentRecordQuery.builder()
            .orderIdIn(orderId2appointment.keySet()).fillTimeSchedules(true).build().list();
        if (CollKit.isEmpty(records)) return;
        Map<String, List<AppointmentRecord>> map = records.stream()
            .collect(Collectors.groupingBy(AppointmentRecord::getOrderId));
        for (String id : map.keySet()) {
            orderId2appointment.get(id).setAppointmentRecords(map.get(id));
        }
    }

    private void fillAppointmentComments(List<AppointmentOrder> appointmentOrders) {
        List<String> orderIds = appointmentOrders.stream()
            .map(AppointmentOrder::getOrderId).collect(Collectors.toList());
        List<AppointmentComment> comments = AppointmentCommentQuery.builder()
            .orderIdIn(orderIds).build().list();
        if (CollKit.isEmpty(comments)) return;
        Map<String, List<AppointmentComment>> map = comments.stream()
            .collect(Collectors.groupingBy(AppointmentComment::getOrderId));
        for (AppointmentOrder order : appointmentOrders) {
            order.setAppointmentComment(CollKit.isNotEmpty(map.get(order.getOrderId())) ?
                map.get(order.getOrderId()).get(0) : null);
        }
    }
}

Performance and Usage

The fill method processes batches; for a single record the overhead of multiple in‑memory joins could increase memory and DB connection usage. In the example, three extra queries replace one complex SQL join, making the logic clearer and reducing the maintenance burden of large SQL statements.

Front‑end callers control aggregation by setting the boolean flags in the query object. If the flags are false, only the primary table is fetched, preserving performance for simple scenarios.

Conclusion

In‑memory aggregation offers a flexible alternative to heavyweight SQL joins, especially for micro‑services or admin back‑ends with many small tables. Developers must weigh the extra queries and memory cost against the benefit of clearer code and easier maintenance, and decide whether to store data redundantly or keep it normalized based on the specific query workload.