This article comprehensively covers permission system design from basic RBAC to an ideal model incorporating role inheritance, constraints, user groups, organizations, and positions, with detailed database schema diagrams for both standard and extended RBAC implementations.
The author evolves a simple Spring Boot file upload to handle 5GB files by implementing 8MB chunked uploads, resumable upload via chunk tracking, instant upload via SHA-256 deduplication, with database schema, REST API design, streaming merge, database-level concurrency control, and automated cleanup of expired uploads.
This article demonstrates how to store four independent notification channel switches (SMS, email, in-app, WeChat) in a single MySQL TINYINT column using bitwise operations, covering schema design, SQL queries, Java enum and utility classes, service and controller layers, MyBatis mapper, extensibility to N channels, and key considerations like indexing, concurrency, and readability.
The article explains how to replace per‑message read flags with a single lastReadSeq column and an unreadCount cache, enabling efficient unread‑message tracking for large group chats and multi‑device synchronization in a Spring Boot chat system.
The article explains how to translate domain‑driven design into concrete database schemas by first shifting focus from tables to domain models, then mapping the four relationship types, choosing an inheritance strategy, and finally adapting the approach for NoSQL databases.
This article explains the four major NoSQL data models—key‑value, document, column‑family, and graph—detailing their structures, strengths, weaknesses, CAP trade‑offs, consistency options, and how to match each model to specific business queries and requirements, including polyglot persistence strategies.
This lesson walks through relational database normalization using a messy enrollment table, showing how to derive functional dependencies, identify candidate keys, detect partial, transitive and BCNF violations, and systematically decompose the schema into student, major, course, teacher and enrollment tables while ensuring lossless joins and dependency preservation.
This article details a complete Spring Boot implementation of a smart vending cabinet that uses WeChat QR‑code scanning to open doors, reports purchased items, and automatically deducts payment via WeChat's no‑password delegated‑deduction API, covering configuration, database design, code examples, and error‑handling strategies.
The author runs a series of real‑world development tasks with Cursor (Composer 2.5) and Codex (GPT‑5.5) to compare how each agent understands directories, creates files, designs a WeChat lottery mini‑program MVP, generates MySQL schemas, evaluates non‑functional requirements, and reflects on token usage, concluding that Cursor excels in IDE‑centric, structured outputs while Codex shines as a terminal‑based coding agent.
The article walks through a company’s three‑year‑old unified multi‑account login design, covering phone‑code registration, optimized password‑optional login, third‑party OAuth integration, a split user‑base/auth schema, its pros and cons, and a carrier‑based one‑click login flow that reduces login time from seconds to milliseconds.
The article explains why major tech companies discourage using NULL in MySQL, covering its meaning as an unknown state, three-valued logic pitfalls such as NOT IN subqueries, index inefficiencies, aggregate function quirks, storage overhead, Java handling issues, and offers practical alternatives like NOT NULL constraints with sensible defaults.
Alibaba’s Java Development Manual mandates that any SQL involving more than three tables must be avoided, a rule that stems from the exponential cost of multi‑table joins in a single‑instance database, prompting engineers to rethink data modeling, adopt denormalization, wide tables, materialized views, CQRS or application‑level assembly instead of relying on complex joins.
This article presents 160 high‑frequency MySQL interview questions, covering fundamentals such as SQL basics, MySQL vs. Oracle vs. SQL Service, normalization rules, permission tables, and more, with a free PDF of the full list for interview preparation.
The article analyzes a company’s three‑year‑old multi‑account unified login system, detailing its original phone‑code design, an optimized password‑optional flow, third‑party OAuth integration, a split user‑basic and user‑auth table schema, and the benefits and trade‑offs of one‑click mobile number authentication.
A mis‑designed phone number column can silently degrade index performance, overload CPU, and cause a cascade of timeouts in a 2‑billion‑user system, but by treating the phone as a domain value object, using a normalized VARCHAR, aligning indexing rules, and applying consistent sharding and migration strategies, you can prevent the database from collapsing under high concurrency.
The article details a comprehensive multi‑account unified login design—including phone‑number registration, optional password login, third‑party integrations, a split user‑basic and user‑auth data model, its pros and cons, and a one‑click mobile number authentication flow—offering practical insights from three years of production use.
This article walks through the complete WeChat Pay integration for an education‑focused mini‑program, covering the business flow, database schema, Java backend implementation for order creation, callback handling, automatic refunds, scheduled tasks, common pitfalls, and practical tips for reliable payment processing.
This article explains why encrypting sensitive fields like passwords, phone numbers, and ID numbers is essential, details a balanced approach using AES‑GCM encryption, HMAC indexes, and masked fields, and shares real‑world lessons from implementing the solution in a production system.
This article explains how combining Go, PostgreSQL, and sqlc creates a Zero‑ORM architecture that restores SQL to the center of high‑performance, highly controllable, and maintainable backend services, covering design principles, layer responsibilities, schema and query patterns, connection‑pool tuning, transaction handling, observability, and practical best‑practice checklists.
A Rust rewrite of SQLite generated largely by an LLM runs a simple primary‑key lookup 20,171 times slower than native SQLite, exposing how seemingly correct code can miss critical system constraints, and illustrating the need for explicit acceptance criteria, benchmark baselines, and governance when using AI‑generated software.
This article walks through a straightforward back‑office permission system, defining permissions as resource collections, presenting a five‑table database schema, and explaining how classic Role‑Based Access Control (RBAC) simplifies permission management for multiple users.
The article explains the structure, advantages, and disadvantages of star, snowflake, and constellation data‑warehouse schemas, shares real‑world experiences, and provides practical criteria—performance, team skill, and business scope—to help practitioners select the most suitable model or combine them effectively.
This article explains how MySQL partition tables and sharding (manual table splitting) both address performance issues caused by massive single‑table data, compares their architectures, lists limitations, outlines ideal use cases, and provides practical guidelines for choosing the right approach.
This article explains multiple database schema designs for hierarchical comment threads, compares their query performance, and shows how Redis can be used to sort hot comments and handle high‑concurrency likes, providing practical solutions for large‑scale comment systems.
This article explains core database design concepts—from relational vs. NoSQL choices and normalization rules to indexing strategies, ACID transactions, SQL optimization, and sharding—using supermarket‑style analogies and concrete exam‑style examples to help readers build fast, safe, and scalable data stores.
The article explains MySQL’s full range of data types—from integers and floating‑point numbers to strings, binary and temporal types—along with constraint options such as NOT NULL, UNIQUE, CHECK, primary keys and AUTO_INCREMENT, and offers practical guidelines on character sets, collations, and best‑practice selection to ensure data integrity, consistency, and optimal storage and query performance.
This guide explains why solid MySQL design matters, walks through the three normal forms with concrete SQL examples, covers primary keys, foreign keys, indexes, various table relationships, an online‑store case study, design recommendations, and performance‑tuning techniques.
This article explains why many enterprises suffer from mismatched "Customer ID" fields, clarifies the distinction between data modeling (the process) and a data model (the result), and walks through conceptual, logical, and physical modeling as well as three common modeling approaches—normative, dimensional, and entity—highlighting their purposes, steps, and trade‑offs.
A well‑defined naming convention for data‑warehouse tables reduces chaos, improves maintainability, speeds up queries, and cuts cross‑team collaboration costs, turning raw data into a strategic asset for modern enterprises.
This article examines how to design a high‑performance comment system by comparing nested and flat (cover‑floor) database models, evaluating adjacency list, path enumeration, and closure table approaches, and outlining write‑asynchronous, cache‑first read strategies for millions of users.
This article breaks down the interview expectations around database normalization, explains the theory and trade‑offs of normalization versus denormalization, provides SQL code examples, compares design impacts, offers best‑practice guidelines, and warns against common misconceptions.
An experienced MySQL practitioner shares a comprehensive production‑grade design and operations guide, covering primary key selection, normalization, indexing, data types, sharding, foreign key trade‑offs, naming conventions, transaction handling, NULL usage, type safety, read/write splitting, caching, triggers, logging, monitoring, partitioning, security, and a three‑layer summary.
This article analyzes the interview focus on MySQL auto‑increment primary key exhaustion, explains the underlying mechanism, outlines preventive design choices and monitoring, and provides detailed emergency response options, best‑practice recommendations, and common pitfalls for robust database management.
When a MySQL table's auto‑increment primary key approaches its maximum value, you can avoid failures by converting the column to BIGINT, switching to UUIDs, using segmented ID generators, composite keys, adjusting the increment step, or applying database sharding.
This article explains how improper use of NULL in MySQL columns can lead to serious production incidents, outlines official recommendations against NULL, demonstrates its impact on queries, indexes, and storage, and provides practical guidelines for when to use or avoid NULL values.
This comprehensive guide walks you through fundamental database design goals, a step‑by‑step lifecycle, nine essential strategies—including normalization, indexing, and security—plus modern distributed and NoSQL considerations, performance tuning, high‑availability tactics, and practical tools for robust data governance.
This article examines MySQL’s handling of nullable columns, comparing the storage implications of saving NULL versus assigning default values, explains InnoDB row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED), and outlines the effects on storage space, indexing, and query behavior.
Although many developers store IPv4 addresses in MySQL using VARCHAR(15), the optimal approach is to store them as a 32‑bit unsigned integer, which saves space, speeds up indexing, and can be easily converted with MySQL's inet_aton and inet_ntoa functions.
An in‑depth guide walks through the challenges of enterprise‑grade large file uploads—covering chunked transfer, resumable uploads, security, audit trails, and a complete set of RESTful endpoints with database schema, state‑machine handling, and both local and cloud storage integration for AI‑driven document processing.
This guide presents a complete database development specification—covering naming, design, data types, index, and SQL rules—with mandatory, recommended, and reference levels to ensure consistent, high‑performance, and maintainable database schemas.
This article explains why phone numbers should be stored as VARCHAR(15~20) rather than INT/BIGINT, covering underlying principles, performance tests, internationalization, security, large‑scale optimization techniques, and a concrete best‑practice checklist for enterprise systems.
When faced with 50 million user rows, using LIKE '%1234' triggers a full table scan, but adding a suffix column, reversing the phone number, leveraging function indexes, or integrating Elasticsearch can reduce query time from minutes to a few milliseconds, each with its own trade‑offs.
The article examines whether a nullable MySQL column should store NULL or a business default, detailing InnoDB row formats, storage overhead, record headers, hidden columns, and the trade‑offs of NOT NULL versus NULL definitions.
This article explains how MySQL stores rows, compares the four InnoDB row formats, and evaluates the trade‑offs of using NULL versus explicit default values for nullable columns, covering storage overhead, indexing behavior, and best‑practice design recommendations.
This article explains how MySQL stores rows using different InnoDB row formats, illustrates the internal layout of variable‑length columns and hidden fields, and compares the trade‑offs of defining columns as NOT NULL versus allowing NULL values, offering practical guidance for database design.
This article explains why permission management is essential, outlines basic and advanced permission models—including RBAC, role inheritance, constraints, user groups, organizations, and positions—and provides detailed table designs for both standard and ideal RBAC implementations, helping developers build scalable, secure access control systems.
The article explains how inconsistent "customer ID" fields across systems stem from a lack of unified data models, defines the difference between data modeling and data models, outlines three modeling stages, and compares three major modeling approaches—normative, dimensional, and entity—highlighting their purposes, processes, and trade‑offs.
This article explains how to design scalable, collision‑free coupon codes by using a custom binary layout, bit‑field allocation, checksum validation, obfuscation, Base62 encoding, and provides complete Java‑style algorithms and database schema for production use.
This article explores how to replace long UUIDs with short, sequential numeric IDs by evaluating Snowflake, MySQL auto‑increment tables, REPLACE INTO deadlock issues, batch allocation strategies, and a final free‑ID table design that balances performance, simplicity, and low ID waste.
Effective Redis key naming is essential for building robust, scalable applications; this guide outlines clear conventions—meaningful names, colon-separated namespaces, concise keys, proper ordering, TTL usage—and provides concrete examples across data types, common pitfalls, and a universal key template to improve readability, maintenance, and performance.
This article explains how to build a production‑grade short‑link service, covering the background of SMS short URLs, Base62 encoding principles, database schema design, request routing, security measures such as custom alphabets, rate limiting, and signed links, and additional management features for operation and monitoring.
This article shares practical guidelines for designing database schemas with meaningful table and column names, covering prefixes, English naming, boolean and timestamp conventions, foreign‑key suffixes, appropriate redundancy, and concrete examples of good and bad designs to help teams build readable and maintainable databases.
The article recounts how a social‑app team faced compliance rejection, realized the risks of storing passwords, phone numbers, IDs in plain text, and then built a pragmatic encryption‑at‑rest solution that balances security, queryability, and operational simplicity.
This guide explains how to design clear and maintainable database schemas by using meaningful prefixes, full English names, consistent suffixes for booleans, timestamps, and foreign keys, and by applying best‑practice naming conventions, redundancy strategies, and relationship modeling to improve readability and performance.
The article explains why the last six digits of Taobao order numbers are constant, revealing the “gene” method that embeds a hashed user identifier to bind orders to specific shards, thereby improving query performance, ensuring balanced data distribution, and supporting scalability in large e‑commerce systems.
This guide demonstrates using the Tongyi Lingma AI agent to automatically design an SQLite database schema, generate SQL, insert sample data, and conduct data analysis with visual charts, covering all required installations, configurations, prompts, and code snippets.
This article explains the concept of Versioned Value, how multi‑version storage supports MVCC and time‑travel queries, and how it resolves write conflicts in single‑leader replication through WAL logs, high‑water marks, and skip‑list indexing, while also discussing its limitations.
This article explains why storing dates as strings is inefficient, compares MySQL DATETIME and TIMESTAMP—including timezone handling and performance trade‑offs—shows practical SQL examples, adds PostgreSQL equivalents, and offers concise recommendations for selecting the optimal time storage format.
This article explores how to replace long Snowflake IDs with concise numeric account IDs by leveraging MySQL auto‑increment tables, addressing deadlock pitfalls of REPLACE INTO, and evaluating alternative batch‑allocation and sharding strategies before presenting a final free‑ID table design.
This article explains essential data sharding techniques for large-scale architectures, emphasizing the priority of vertical splitting by business modules, followed by horizontal partitioning of hot tables, and outlines stable sharding rules, hotspot avoidance, and minimizing cross‑shard transactions to ensure optimal performance.
Facing gaps in database education, Beijing Information Technology University launched a GaussDB‑based textbook project to blend theory with hands‑on practice, producing a four‑part, thirteen‑chapter resource that covers fundamentals, SQL, design, and management, while supporting curriculum reform and industry collaboration.
This article investigates why MySQL recommends auto‑increment primary keys over UUIDs by building three test tables, measuring insert speeds with Spring JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and drawbacks of each approach.
This article compares five common database extension strategies—from simple MySQL column additions to a hybrid MySQL‑HBase solution—detailing their implementation, advantages, drawbacks, and ideal scenarios, helping architects select the most scalable and maintainable design for evolving business data requirements.
This article explains the materialized view pattern, describing why pre‑populated views are created when source data formats hinder efficient queries, how they improve performance as disposable caches, and the key considerations, trade‑offs, and scenarios for adopting this approach.
An interview-style SQL challenge asks you to identify records where a professor teaches a course belonging to a different college than their own, providing table definitions, sample data, and the expected result, while inviting readers to devise the correct query.
When designing a database for billions of phone numbers, using INT or BIGINT leads to overflow, data‑integrity loss, and inefficient queries, while VARCHAR(20) preserves formatting, supports international extensions, simplifies validation, and avoids common development pitfalls.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
The article examines the challenges of generating short, user‑friendly numeric account IDs, evaluates Snowflake and MySQL auto‑increment approaches, discusses deadlock issues with REPLACE INTO, and presents a final segment‑based solution that allocates ID blocks per login server while avoiding waste and concurrency problems.
The article explains why establishing a unified data model is essential, differentiates data modeling from data models, outlines three modeling stages, compares normative, dimensional, and entity modeling methods, and provides practical steps and diagrams to help organizations build robust, business‑driven data architectures.
This article examines the difficulties of adding new fields to a core billion‑row MySQL table—including locking, page splitting, and index degradation—and presents a configuration‑driven, three‑layer architecture that uses JSON extension fields, extension tables, and Elasticsearch to achieve safe, scalable dynamic schema evolution.
The article examines an interview question about storing 2 billion phone numbers, explains why int cannot hold 11‑digit numbers, advocates using VARCHAR(20) for flexibility and data integrity, and outlines common pitfalls and best practices for database design.
This article explains why database normalization is essential, defines the first three normal forms with examples, discusses their advantages and disadvantages, and illustrates table relationships and practical design considerations to help you create efficient, low‑redundancy relational databases.
When designing a schema for two‑billion phone numbers, use a VARCHAR(20) column with utf8mb4 Unicode, a unique index, and proper validation/encryption rather than a 32‑bit INT, because strings preserve leading zeros, international prefixes, extensions, and support business extensibility, fault tolerance, and future changes.
This article discusses the problem of MySQL auto‑increment ID exhaustion and presents six practical solutions—including changing column type to BIGINT, using UUIDs, segmenting ID generation, composite keys, adjusting auto‑increment steps, and database sharding—to ensure scalability and uniqueness.
This article explains what wide tables are, why they are controversial, outlines three common design pitfalls with practical avoidance tips, and introduces three key technologies—ClickHouse, Cassandra, and Hudi/Iceberg—to help engineers build performant, maintainable wide‑table solutions in data warehouses.
The article explains why using UUIDs as primary keys in MySQL large tables leads to poor index efficiency, slower inserts and queries, and costly index refreshes on updates, and then presents practical optimization techniques such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.
This article explains InnoDB clustered and secondary indexes, their physical B‑tree storage, page‑size and fill‑factor settings, the three‑phase sorted index build process, and the design, tables, cache, document‑ID handling, transaction semantics, and monitoring of InnoDB full‑text indexes.
This article investigates MySQL’s recommendation against UUIDs, comparing auto‑increment, UUID, and random snowflake keys through insertion speed tests and index structure analysis, revealing why auto‑increment keys outperform others, while also discussing the drawbacks of each approach.
This article investigates MySQL's recommendation against using UUIDs or non‑sequential keys as primary keys by creating three tables, benchmarking insert and query speeds with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the trade‑offs of auto‑increment, UUID, and random long keys.
This guide covers practical best‑practice tips for database table design, including clear naming conventions, appropriate field types and lengths, primary‑key strategies, index usage, storage‑engine choices, NOT NULL handling, foreign‑key considerations, charset and collation settings, and handling of large fields.
This guide introduces the open‑source DrawDB tool, walks through Docker‑based installation, demonstrates designing tables and relationships using a mall e‑commerce permission module example, and covers SQL import/export, theme switching, and template customization, helping developers efficiently create database schemas.
The article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and finally SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for multiple business units and their dynamic product lists while respecting development constraints.
This article experimentally investigates whether the length of VARCHAR columns (e.g., 50 vs 500) affects MySQL storage size and query performance, covering table creation, bulk data insertion, storage queries, index and full‑table scans, and explains the underlying reasons for any differences observed.
This article explains the concept, advantages, and technical choices of multi‑tenant architecture, then details a design using Spring Boot and Spring Cloud, covering database strategies, deployment isolation, tenant management, code examples, and step‑by‑step implementation for SaaS applications.
This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.
Activiti is a BPMN workflow engine that provides components such as Process, Repository, Runtime, Task, and History services, stores definitions, runtime data, and history in a relational database schema, and is used by deploying BPMN diagrams, starting instances, and managing tasks via its Java API.
This article examines why a MySQL table with a case‑insensitive collation returns uppercase brand names when searching for lowercase input, analyzes the underlying charset and collation settings, and proposes backend pagination with a case‑insensitive fuzzy search and a unique index to reliably prevent duplicate brand records.
Learn how to leverage Navicat’s powerful database design, structure synchronization, and backup features using the Mall e‑commerce project as a case study, with step‑by‑step instructions, screenshots, and tips for managing MySQL models, tables, foreign keys, and environment migrations.
This tutorial explains how to design a MySQL table for hot‑search records, generate Java entity and mapper classes using MyBatis Generator, create unique IDs for each entry, and implement scheduled Java crawlers for Douyin and Baidu hot‑search data that persist the results via Spring‑Boot services.
This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.
Learn how to install and use the open‑source DrawDB database design tool with Docker, explore its visual modeling features, SQL import/export capabilities, theme and template options, and see a real‑world example designing the permission module of a SpringBoot3‑Vue e‑commerce project.
This article explains how to design a robust comment module with multi‑level threading, efficient database schemas, asynchronous write pipelines, and performance tricks such as hot‑comment caching and read/write separation to handle high traffic loads.
The article explains that storing IPv4 addresses as a 32‑bit UNSIGNED INT in MySQL saves storage and index space, enables faster range queries, outlines conversion functions like INET_ATON/INET_NTOA, discusses drawbacks such as readability, and provides Java utilities for IP‑long transformations.
This article examines the challenges of scaling store settlement calculations, evaluates several Java expression engines, selects MVEL for its performance and community support, and details the architectural redesign, database schema, caching strategy, and step‑by‑step migration process for integrating the engine into a backend system.
This tutorial walks through designing a complete logical and physical database model for a Google Calendar clone, covering entities, attributes, relationships, handling all‑day and time events, recurrence rules, slots, and SQL table creation, while illustrating minimal‑modeling techniques.
Both TINYINT(1) and BIT(1) are common MySQL data types for storing Boolean values, but they differ in storage size, range, syntax, compatibility, and ideal use cases, with TINYINT offering broader integer support and portability, while BIT provides true single‑bit storage for strict Boolean fields.
This article investigates whether MySQL VARCHAR(50) and VARCHAR(500) occupy identical storage and how column length impacts query performance, providing step‑by‑step table creation, bulk data insertion, storage queries, and detailed benchmark results with analysis of the underlying causes.
This article presents Alibaba's concise 10‑point MySQL schema standard, covering boolean field naming, lowercase identifiers, singular table names, reserved word avoidance, index naming conventions, decimal usage, fixed‑length char, variable‑length varchar/text, mandatory audit fields, and the prohibition of physical deletions, offering practical guidance for clean database design.
This article investigates MySQL's recommendation against using UUID or non‑sequential random IDs as primary keys by benchmarking auto‑increment, UUID, and Snowflake‑derived keys, analyzing their insertion speed, index behavior, and discussing the advantages and drawbacks of each approach.
This article investigates whether defining a VARCHAR column with a larger length affects MySQL storage space and query performance by creating two tables with VARCHAR(50) and VARCHAR(500), inserting one million rows, and measuring storage usage and execution times for various queries.
