skills.rest
entropy-cloud
entropy-cloudCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

nop-database-design

Defines database design conventions for table naming, primary keys, indexes, and standard fields.

693|100|Updated Aug 18, 2022
Search Tags:#database design#orm#naming conventions#Schema#ddl#primary key#nop platform
One-click install
npx skills add https://github.com/entropy-cloud/nop-entropy --skill nop-database-design
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: nop-database-design
Source: https://github.com/entropy-cloud/nop-entropy/tree/main/.opencode/skills/nop-database-design
Command: npx skills add https://github.com/entropy-cloud/nop-entropy --skill nop-database-design

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Designing consistent relational database schemas across teams is error-prone: inconsistent naming, unsafe auto-increment primary keys, missing audit columns, and poorly planned indexes cause maintenance and migration pain. This Skill provides a complete set of database design conventions for the Nop platform so every table follows the same rules.

Core Features & Use Cases

  • Naming Conventions: Enforces snake_case singular table names with module prefixes (e.g., nop_auth_user), column naming rules, and index naming patterns (pk_, uk_, ix_, fk_).
  • Primary Key & Standard Fields: Mandates application-generated keys (UUID v7, snowflake, NanoID) instead of AUTO_INCREMENT, plus required audit fields (created_by, create_time, updated_by, update_time), optimistic locking (version), and logical deletion (del_flag).
  • Ready-to-Use Templates: Provides standard business table, many-to-many relation table, and log table DDL templates, plus a cross-database type mapping for MySQL, PostgreSQL, Oracle, and SQLite.
  • Use Case: When designing a new audit module, apply the conventions to produce nop_audit_client with proper primary key strategy, audit columns, indexes, and a migration script that passes the built-in design checklist.

Quick Start

Ask the AI to design a new database table for your module following the Nop database design conventions, including naming, primary key strategy, audit fields, and indexes.

Frequently Asked Questions about nop-database-design

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I name database tables and columns in the Nop platform?▼

Table names use lowercase snake_case in singular form with a module prefix like nop_auth_user. Columns use snake_case in the database and camelCase in code, with _id suffixes for foreign keys and _time or _date suffixes for temporal columns.

Why should I avoid AUTO_INCREMENT primary keys in database design?▼

Auto-increment keys cause conflicts in distributed deployments, complicate cross-database migration, and break multi-source data merging. The conventions recommend application-generated keys such as UUID v7, snowflake IDs, NanoID, or ULID stored as VARCHAR(32).

What standard fields must every business table include?▼

Every business table must include created_by, create_time, updated_by, and update_time audit columns. A version column for optimistic locking and a del_flag column for logical deletion are recommended, with optional remark and description fields.

Does this database design convention support databases other than MySQL?▼

Yes, the conventions apply to MySQL, PostgreSQL, Oracle, and SQLite. An appendix provides a data type mapping table, for example VARCHAR(32) primary keys across all four and TINYINT(1), BOOLEAN, NUMBER(1), or INTEGER for booleans.

How should many-to-many relationship tables be designed?▼

Use a junction table named with both entities, such as nop_auth_user_role, with a composite primary key of the two foreign key columns. Include the standard audit fields and version column, and add optional extension columns when needed.

What are the index design limits and rules for these tables?▼

Foreign key columns must be indexed, and composite indexes follow the leftmost prefix rule with high-selectivity columns first. A single table should have no more than about 10 indexes, and index names follow pk_, uk_, ix_, and fk_ prefixes.

Related Skills

Explore More Software Engineering Skills→
bs-koobs-koo

generate-erd-guide

Generate ERDCloud-ready DDL and DE-08 table-definition drafts from requirements and screen lists.

Community
Intermediate
podversepodverse
6

podverse-orm-patterns

Document TypeORM entity, service, migration, and query builder patterns.

Official
Advanced
cipherstashcipherstash
156

stash-dynamodb

Encrypt and decrypt DynamoDB items with CipherStash field-level encryption.

Official
Advanced
EmeaAppGbbEmeaAppGbb

data-model-extractor

Extract database entities, relationships, and constraints from ORM models into Mermaid ERDs.

Official
Advanced