Database/Schema Design Principles

Tacnode is fully compatible with the PostgreSQL ecosystem, organizing data management into three levels: database, schema, and table. This design is crucial for ensuring system maintainability, scalability, and performance. A well-designed database and schema structure can help reduce complexity, improve performance, and ensure data integrity and security.

Here are some key principles for database and schema design in Tacnode.

Database Design Principles

Clearly Define Database Responsibilities

• Each database should have a clear role. For example, one database can be dedicated to transactional data, while another can be used for analytical tasks. This helps avoid mixing different types of data and application logic, reducing management complexity.

Reasonable Database Separation

• Store data for different applications or modules in separate databases. Sharing a database among multiple applications can lead to data access conflicts or complex transaction management issues. Clearly defined database boundaries help simplify management, backup, and recovery processes.

Minimize Data Redundancy

• Avoid unnecessary data redundancy in the database. Use normalization techniques to reduce redundant data storage and ensure data consistency.

Choose Appropriate Data Types

• Use data types that best fit business needs. For example, avoid storing integer data as string types, which not only saves storage space but also improves query efficiency.

Scalability and Performance

• As data volume increases, databases may face performance bottlenecks. Consider query performance, indexing, partitioning, and other optimizations during design. Ensure the database can scale with growing demands.

Database Security

• Avoid granting excessive permissions to all users. Use appropriate roles and permissions management to ensure data security.

• In the same Tacnode instance, different databases are independent and cannot access each other.

Schema Design Principles

Organize Database Objects Logically

• In Tacnode, schemas are used to logically group database objects such as tables, views, and indexes. A well-designed schema helps manage complexity and improves query and maintenance efficiency.

• Typically, schemas can be divided based on business modules, functions, or applications. For example, data for the finance module can be placed in a finance schema, while user module data can be in a users schema.

Avoid Over-Segmentation of Schemas

• While distributing objects across multiple schemas helps isolate and organize data, over-segmentation can increase management complexity. For small applications or systems without complex modules, using a few schemas or even placing all objects in the public schema may be sufficient.

Standardize Schema Naming

• Use consistent and descriptive naming conventions. For example, schemas can be divided by function or domain, such as accounting, inventory, marketing, etc.

Control Access Permissions to Schemas

• In Tacnode, different access permissions can be assigned to different schemas, allowing certain users to access only specific schemas. This design helps achieve permission isolation between different users or applications.

Coupling Between Tables and Schemas

• Within the same database, tables in different schemas can access each other, meaning they can be shared.

• Tables in different schemas can have the same name, as tables are independent within their schemas.

• Tables within the same schema should have a high degree of coupling, meaning they are relatively closely related. Table design should avoid frequent cross-schema joins, as this can impact query performance.

Avoid Cross-Schema Dependencies

• Although Tacnode allows cross-schema access, it's best to minimize dependencies between different schemas to avoid complexity. Cross-schema dependencies can complicate code or query maintenance and migration.

Best Practices

Use the public Schema with Caution

• Tacnode creates a public schema by default. While you can place all objects in this schema, it's better to create custom schemas to organize different datasets as the system becomes more complex.

Use Partitioning

• For large tables, consider using Tacnode's partitioning feature. Partitioning can improve the management and operational efficiency of large datasets, especially for periodic rolling deletions. For example, partition by date or geographic location.

Use Indexes Wisely

• Indexes are crucial for query performance but consume disk space and can affect write performance. Choose indexes based on common query patterns during design.

Focus on Backup and Recovery Strategies

• Database design should consider backup, recovery, and disaster recovery strategies. For example, consider dividing the database into multiple logical units (schemas) to quickly recover a specific business module instead of the entire database in case of failure.

Perform Regular Cleanup

• Consider data lifecycle management in database design. Set reasonable archiving, deletion policies, and triggers to ensure the database is not filled with outdated data.

Summary

Good database and schema design can improve performance, simplify maintenance, and enhance system scalability and security. Consider different business needs, data scale, query patterns, and data access permissions during design. Avoid over-complication while focusing on a reasonable organizational structure and naming conventions.