Choosing the Right Database Schema for Dynamic Business Field Expansion

During business development, adding fields is common; choosing how to store new fields is crucial for extensibility.

Options

1. Simplest: Add column directly to MySQL table

Implementation: ALTER TABLE ... ADD COLUMN ...

Pros:

Simple and fast iteration

Cons:

Frequent schema changes

Table bloat and index efficiency decline (MySQL row size limit 65,535 bytes; TEXT/BLOB stored separately using 9‑12 bytes)

Applicable scenarios:

Early stage where fields are added frequently

Field is common to all records

2. Aggregate fields by business domain (incremental update)

Implementation: store new fields per domain in a JSON column, e.g., field_1 for domain 1, field_2 for domain 2.

Pros:

Business aggregation: same domain information resides in one field, no DDL for each new field

Cons:

Each update requires reading the current value, merging changes, and writing back

Concurrency control needed (optimistic or pessimistic lock)

MySQL row size limit still applies

Applicable scenarios:

Small projects; generally not recommended

3. Vertical partitioning by business domain (split tables)

Implementation: separate tables for related domains, e.g., order_info , order_payment , order_logistics .

Pros:

Complete decoupling of business domains; each table evolves independently

Query only needed tables, improving performance

Cons:

Each vertical table still requires column additions

Ease of future expansion depends on how well the split matches business logic

Applicable scenarios:

Business model is stable

Project has clear business boundaries

4. Main table + dynamic extension table (linked by business ID)

Implementation: Main table stores core fields (business ID, common keys). Extension table stores extension_key and extension_value (JSON or other format) linked by business ID.

Pros:

Dynamic fields without DDL changes

Query by extension key improves lookup performance

Cons:

Extension data stored as string; requires custom parsing and cannot be filtered directly

Applicable scenarios:

Preferred long‑term solution for flexible schema evolution

5. MySQL main table + HBase extension table

Implementation: MySQL stores core structured data; HBase stores sparse, dynamic fields. Business ID is part of HBase rowKey (e.g., {suffix}_{biz_id} ). For conditional queries, export to Elasticsearch.

Pros:

Supports massive columns with efficient sparse storage

Advanced version of “main table + extension table”

Cons:

Extension data not easily queryable without exporting to ES

Weak transaction support across MySQL/HBase (only eventual consistency)

Higher development and operational complexity

Applicable scenarios:

Large data volume requiring part of the extension data in HBase

Dynamic fields reaching thousands or tens of thousands

Conclusion

Fields applicable to all records can be added directly to the MySQL table.

In early stages, prioritize the “main table + dynamic extension table” pattern for a balance of flexibility and complexity.

When extension data volume grows large or dynamic fields reach thousands, consider upgrading to the “MySQL main table + HBase extension table” pattern.

If complex queries are needed, add Elasticsearch to build secondary indexes.