Database Design & QueryDSL setup

Relevant source files

The following files were used as context for generating this wiki page:

• src/main/java/org/example/coding_convention/config/QuerydslConfig.java
• src/main/java/org/example/coding_convention/project/repository/ProjectQueryRepository.java
• src/main/resources/data.sql
• src/main/java/org/example/coding_convention/project/model/Project.java
• src/main/java/org/example/coding_convention/file/model/Files.java
• src/main/java/org/example/coding_convention/project_member/model/ProjectMember.java
• src/main/java/org/example/coding_convention/chat/model/Chats.java
• src/main/resources/application.yml

Introduction

The database design for PhanesEditor is built on MariaDB and managed through Spring Data JPA with a strong emphasis on relational integrity and real-time collaboration support. The system organizes data around projects, users, and their interactions, including files, chat messages, and membership statuses. To facilitate complex searching and dynamic filtering, the project implements QueryDSL, providing a typesafe way to construct queries for project discovery and management.

Sources: src/main/resources/data.sql:1-12, src/main/resources/application.yml:58-61, README.md

Database Schema Design

The core of the system is the project table, which serves as the central entity connecting users, files, and communication channels. The schema uses foreign key constraints with ON DELETE CASCADE to maintain referential integrity across related modules.

Entity Relationship Diagram

The following diagram illustrates the relationships between the primary tables in the system.

Core Table Definitions

Table	Key Fields	Constraints	Description
users	idx, email, nickname	UNIQUE(email)	Stores user identity and authentication metadata.
project	idx, project_name, creator_id	FK(creator_id)	Central entity for collaborative workspaces.
files	idx, file_name, file_path	UNIQUE(name, path)	Stores file metadata and S3 paths.
project_member	idx, user_id, project_id	FKs to user/project	Manages project access and roles (ADMIN/USER).
chats	idx, project_id, user_id	FKs to user/project	Stores real-time communication history.

Sources: src/main/resources/data.sql:41-137, src/main/java/org/example/coding_convention/project/model/Project.java:23-38

QueryDSL Configuration & Implementation

QueryDSL is configured to provide a JPAQueryFactory bean, which allows for programmatic, typesafe query building. This is primarily used for the advanced project search feature.

Configuration Setup

The QuerydslConfig class initializes the JPAQueryFactory using the application's EntityManager.

@Configuration
public class QuerydslConfig {
    private final EntityManager em;
    public QuerydslConfig(EntityManager em) { this.em = em; }

    @Bean
    public JPAQueryFactory jpaQueryFactory() {
        return new JPAQueryFactory(em);
    }
}

Sources: src/main/java/org/example/coding_convention/config/QuerydslConfig.java:1-25

Advanced Project Search Logic

The ProjectQueryRepository utilizes QueryDSL's BooleanExpression to implement dynamic filtering. This allows searching projects by name, creator email, and programming language simultaneously.

Search Implementation Details:

• Fetch Joins: Used to prevent N+1 problems when accessing user (creator) information during project listing.
• Dynamic Filters: Methods like projectNameContains and emailContains return null if the input is blank, causing QueryDSL to ignore that specific WHERE clause condition.
• Pagination: Integrated using offset(), limit(), and a separate fetchCount() for PageImpl construction.

Sources: src/main/java/org/example/coding_convention/project/repository/ProjectQueryRepository.java:23-74

Data Persistence Strategy

The application uses MariaDB and Hibernate for persistence. The application.yml configures the database platform and connection details.

Property	Value	Description
spring.datasource.driver-class-name	org.mariadb.jdbc.Driver	Database driver for MariaDB.
spring.jpa.hibernate.ddl-auto	none	Schema is managed via data.sql rather than auto-generation.
spring.jpa.properties.hibernate.default_batch_fetch_size	100	Optimization for collection loading (used in Project entity).
spring.sql.init.mode	always	Ensures data.sql runs on every startup.

Sources: src/main/resources/application.yml:50-70, src/main/java/org/example/coding_convention/project/model/Project.java:48-52

Summary

The Database and QueryDSL setup provides a robust foundation for PhanesEditor. By using a relational schema with explicit constraints in data.sql and combining it with the dynamic querying capabilities of QueryDSL, the system ensures data integrity while offering high-performance, filterable access to project and user data. The use of fetch joins and batch fetching further optimizes the application for handling the complex relationships inherent in a collaborative editor environment.