database-design

BK-CI Database Design

Applicable Scenarios

Just writing a temporary query SQL to troubleshoot data
Just changing business code, without involving the persistence model and table structures
The requirement is essentially API design, module ownership, or permission model, not database design

First confirm which service the data belongs to. BK-CI adopts microservice decomposition, and it is forbidden to share a database across services just for query convenience.
First decide the role of the table, then decide the fields and indexes:
- Main entity table: stores core business state
- Relation table: expresses many-to-many or mapping relationships
- Summary table: carries the aggregated read model
- History table: carries high-frequency append or archived data
Design indexes based on query paths first, do not pile up fields first. The most commonly used query conditions, sort fields, and unique constraint fields should be prioritized in index design.
DDL changes must go through script management; do not manually modify the online structure directly in the database.
Consider compatibility before changes:
- Whether old fields are still read by old version code
- Whether new fields need default values
- Whether phased release is needed
For large table problems, first determine whether it is indexing, archiving, hot/cold tiering, or sharding; do not default directly to sharding.
For JSON, large text, and state snapshot type fields, clarify why they cannot be split into tables, to avoid making the main table a "universal storage".

Script naming and version directories must remain consistent; for detailed rules see reference/1-script-management.md
For details on sharding and routing strategies see reference/2-sharding.md
Table structure design should serve service boundaries and query patterns, rather than pursuing the theoretically "most complete fields"
Problems that can be solved by aggregation at the code layer should not be prioritized through cross-service database coupling

Only looking at the write model, not the read path, resulting in indexes that completely fail to match real queries
First building an "oversized wide table", then relying on patch-style field accumulation to solve all problems later
Directly reusing another service's database for convenience, causing service boundaries to become invalid
Treating sharding as the default option for performance optimization, ignoring archiving, summarization, and hot/cold splitting
Only changing DDL without evaluating rollback, compatibility, and canary release paths