How to Design Scalable Databases and Application Layers for Business Systems

When designing business systems, extensibility must be considered alongside high availability and scalability, focusing on how to flexibly handle changing requirements without accumulating code smells.

Database Design Scalability

Scalability involves two aspects: database splitting and database design. Splitting solves expansion in non‑clustered environments, while design improves scalability and performance without changing the underlying IT infrastructure.

Horizontal and vertical splitting

Vertical splitting separates tables into different databases based on business domains, aligning with microservice concepts. Horizontal splitting distributes rows of the same table across multiple databases (e.g., by region or organization) while keeping identical schemas, and should be transparent to the upper‑layer application.

Vertical splitting is essentially the same as database partitioning in microservice architectures; horizontal splitting addresses performance limits of a single service's database.

To hide the complexity of horizontal splitting, a DaaS (Database‑as‑a‑Service) layer or middleware is introduced, allowing applications to interact with a unified logical database.

Examples of MySQL middleware include early projects such as Cobar, Amoeba, 360 Atlas, and Mycat. The architecture of Mycat is illustrated below:

Although modern middleware supports distributed transactions, it still cannot fully cover all SQL features, and challenges such as data routing and partitioning remain. Newer solutions like TiDB and PolarDB address these issues more effectively.

Design techniques for database extensibility

Common practices include reserving flexible columns in tables, storing additional attributes as JSON strings, or adopting a vertical table (entity‑attribute‑value) model for highly variable fields, though the latter can complicate joins.

Configuration tables (data dictionaries, global variables, code‑generation rules) can externalize frequently changing business data, making them manageable without modifying middleware configuration files.

For massive tables (e.g., logs with >500 million rows), partitioning by day and adding secondary indexes can dramatically improve query performance.

Application Layer Extensibility

When a business form requires new fields, pre‑allocated columns or JSON columns enable easy extension, and UI visibility can be controlled via configuration.

Workflow engines allow process modifications (adding/removing nodes, changing participants) without code changes.

Stable interfaces are crucial; they can be extended by adding fields or a JSON‑based extension dictionary.

Complex business rules are often externalized via rule engines, but extensive scripting may negate benefits. A practical approach is to extract rule logic into a standard interface and invoke it as needed.

//ISaveBefore.Process(Form Data);
//Save();
//ISaveAfter.Process(Form Data);

Implementing pre‑ and post‑save hooks enables custom processing without altering core logic, supporting multi‑tenant customizations while keeping the core product unchanged.

ISaveBefore.Process(Form Data){
    // Separate rule items
    BusinessRule1();
    BusinessRule2();
    BusinessRuleN();
}

Configuration tables can control which rules are active for a given client, providing flexible, code‑free customization.

SOA‑Based Extensibility Design

SOA emphasizes assembling and orchestrating services; changes in upper‑layer applications often require only re‑composition of services, not modifications to the services themselves, aligning with modern middle‑platform concepts.

The architecture can be visualized in three layers: platform, service, and application. The service layer offers shared API capabilities; the application layer (thin apps or micro‑frontends) follows a three‑tier model:

Data layer – accesses shared master data and private component data via DaaS or domain services.

Business logic layer – implements functionality by composing platform‑provided technical and business services, either through code or visual modeling (e.g., BPEL).

Presentation layer – uses web technologies (JSP, HTML, jQuery, Vue, React, Angular) to invoke logic‑layer services and integrate multiple business components.

Business System Extensibility Summary

Effective extensibility requires coordinated design across database, application, rule, and UI layers; it must support both increased concurrency and evolving business requirements while minimizing recompilation and deployment. Over‑emphasis on extensibility can degrade performance, so a balanced approach is essential.