Database Selection and Rationale

The project uses MySQL as its primary database. This choice is driven by several practical advantages: MySQL offers exceptional stability with minimal downtime occurrences, operates under an open-source license with zero licensing costs, features a compact footprint with straightforward installation and maintenance requirements, and provides reliable performance for typical web application workloads.

Basic CRUD Operations

SELECT Queries:

SELECT [DISTINCT] column1, column2, CONCAT(first_name, ' ', last_name) AS full_name
FROM users_table
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 10;

UPDATE Operations:

UPDATE products 
SET price = 29.99, stock_quantity = 150
WHERE product_id = 1001;

INSERT Operations:

INSERT INTO employees (emp_name, department, salary) 
VALUES ('John Smith', 'Engineering', 75000);

INSERT INTO employees (emp_name, department, salary) 
VALUES 
    ('Alice Johnson', 'Marketing', 65000),
    ('Bob Williams', 'Sales', 70000);

DELETE Operations:

DELETE FROM orders 
WHERE order_date < '2023-01-01' AND status = 'completed';

Database Logical vs Physical Structure

Logical Structure: The logical design phase transforms user requirements obtained during analysis into an information structure, represented as a conceptual model. This involves converting E-R diagrams into relational schemas following database normalization rules.

Normalization encompasses three fundamental forms:

• First Normal Form (1NF): Guarantees atomicity of column values—each column must contain only indivisible values.
• Second Normal Form (2NF): Ensures every non-primary key column depends entirely on the primary key, eliminating partial dependencies.
• Third Normal Form (3NF): Eliminates transitive dependencies, meaning non-key columns must not depend on other non-key columns.

Physical Structure: The physical design phase determines how data is actually stored on disk, including selection of appropriate data types, indexing strategies, and storage parameters optimized for the target database system.

Entity Relationship Types

One-to-One (1:1): A single instance of entity A corresponds to exactly one instance of entity B. Example: Each employee has one unique employee badge.

One-to-Many (1:N): One instance of entity A can relate to multiple instances of entity B. Example: A single department contains numerous employees.

Many-to-Many (M:N): Multiple instances of entity A relate to multiple instances of entity B. This relationship requires an intermediate junction table. Example: Students enroll in multiple courses, and each course has multiple students.

Table and E-R Diagram Design Process

Step 1 - Requirements Analysis: Gather user requirements through interviews and documentation to identify entities and their attributes.

Step 2 - Conceptual Design: Create E-R diagrams that abstract real-world entities and their relationships.

Step 3 - Logical Design: Transform E-R diagrams into relational table structures, applying normalization principles.

Step 4 - Physical Design: Define specific data types, field lengths, and indexes based on performance requirements and storage considerations.

Foreign Key Constraints

Foreign keys were deliberately avoided in this project. The rationale centers on several practical concerns: foreign key constraints introduce additional overhead during insert and update operations, can cause internal table locks during complex queries, potentially create deadlock scenarios under high concurrency, and are uncommon in production environments where joins are typically handled through application-level logic.

Table Associations

Tables connect through primary and foreign key relationships:

• Primary Key: Uniquely identifies each record, cannot be NULL, and must be unique. In MyBatis-Plus, use @TableId(type = IdType.AUTO) for auto-incrementing integer keys.
• Foreign Key: References the primary key of another table, establishing referential integrity and enabling relationship traversal.

@TableField("dept_id")
private Long departmentId;

MyBatis vs MyBatis-Plus

MyBatis provides SQL mapping capabilities, allowing developers to configure mappings between Java objects and SQL statements using XML or annotations. It handles result set mapping and prevents SQL injection when used correctly.

MyBatis-Plus extends MyBatis with additional productivity features: built-in CRUD operations via BaseMapper, automatic primary key generation, condition wrapper classes for dynamic queries, and code generation capabilities. The wrapper API simplifies complex query construction without writing raw SQL.

// MyBatis-Plus QueryWrapper example
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("status", "active")
        .like("username", searchTerm)
        .orderByDesc("last_login");
List<User> users = userMapper.selectList(wrapper);

#{} vs ${} in MyBatis

#{} uses JDBC prepared statement placeholders, treating values as bound parameters. This approach effectively prevents SQL injection attacks and is the recommended approach for user-provided data.

${} performs direct string substitution, inserting values literally into the SQL statement. While useful for dynamic identifiers like table or column names, it exposes the application to SQL injection risks when handling untrusted input.

Rule of thumb: Prefer #{} for all value bidnings; use ${} only when dynamic identifiers are unavoidable.

JDBC Connection Requirements

Four essential components for database connectivity:

1. Driver Class: JDBC driver class name (e.g., com.mysql.cj.jdbc.Driver)
2. Connection URL: Database location and parameters (e.g., jdbc:mysql://localhost:3306/project_db)
3. Username: Database authentication credential
4. Password: Database authentication credential

Data Redundancy Considerations

Redundant fields exist in certain tables as a performance optimization strategy. For instance, the job posting table includes denormalized fields for recruiter information to reduce join operations during frequent read queries. This represents the classic space-time tradeoff where storage overhead improves query response times.

Pagination Implementation

SELECT * FROM products
WHERE category = 'electronics'
ORDER BY price ASC
LIMIT 10 OFFSET 20;

Application-side pagination also requires passing parameters: page number, page size, and sort configuration to calculate the appropriate offset.

Fuzzy Search Implementation

SELECT * FROM customers
WHERE customer_name LIKE '%张%'
  AND address LIKE '%四川%';

The application conditionally appends the LIKE clause only when search parameters are provided, preventing unnecessary conditions.

The 1=1 Pattern in Dynamic SQL

When constructing SQL queries programmatically, the 1=1 pattern serves as a constant-true anchor that simplifies concatenation logic:

Without 1=1:

SELECT * FROM users WHERE AND status = 'active'  -- syntax error

With 1=1:

SELECT * FROM users WHERE 1=1 AND status = 'active'  -- valid

This approach eliminates conditional handling for the first WHERE clause during string building.

VARCHAR vs CHAR

CHAR is fixed-length storage. When storing "Hi" in a CHAR(10) field, it pads with 8 trailing spaces. This makes retrieval faster but wastes storage space.

VARCHAR is variable-length, storing only the actual data plus 1-2 bytes for length tracking. Storing "Hi" in VARCHAR(10) uses only 2-3 bytes. This is more storage-efficient but has slightly higher processing overhead.

Choose CHAR for fields with consistent lengths (country codes, phone prefixes), and VARCHAR for variable-length data (names, descriptions, addresses).

Understanding ER Diagrams

An Entity-Relationship (ER) diagram is a visual representation of data entities and their interconnections, serving as a conceptual model before physical implementation.

Key Components:

Symbol	Meaning
Rectangle	Entity type
Diamond	Relationship between entities
Oval	Attribute
Underlined oval	Primary key attribute
Lines	Connections between elements

Relationship Notations:

• 1:1 — One entity relates to exactly one other entity
• 1:N — One entity relates to multiple other entities
• M:N — Multiple entities relate to multiple other entities

E-R diagrams operate at the conceptual level, while database tables represent the logical implementation of this model.

Why ER Diagrams Matter

ER diagrams provide a standardized communication tool between developers and stakeholders, revealing data requirements before technical implementation begins. They expose potential normalization issues early, reducing costly refactoring later. The diagram serves as documentation that helps new team members understand the data model quickly.