Database Libraries

A collection of database schemas designed for various business domains and services. This repository provides standardized, documented database implementations that can be used as starting points for different types of applications.

🎯 Overview

This repository contains a library of database schemas, each tailored for specific business domains and services. Each database implementation includes:

• SQL DDL scripts (tables | optional: views, indexes, functions, procedures, triggers)
• Documentation (discribe abot business logical and analyze database).
• Database diagrams (text and visual formats).
• Production features (including audit trails, security measures, and performance optimizations)
• Standardized conventions (ensuring consistency across all implementations)

📁 Repository Structure

database-libraries/
├── databases/                   # Database implementations
│   ├── [future_service_1]/      # Each database can serve a service
│   ├── [future_service_2]/
│   └── [future_service_N]/
├── documents/                   # Documentation and conventions
│   └── convention.md            # Coding standards and conventions
├── tools/                       # Database utilities and tools
└── README.md                    # This file

🚀 Getting Started

Prerequisites

• PostgreSQL 13+ (recommended: PostgreSQL 15+)
• Required Extensions:
  • uuid-ossp - For UUID generation
  • pgcrypto - For password hashing and encryption

Installation

1. Clone the repository

   git clone https://github.com/TrongAJTT/postgres-database-library.git
   cd database-libraries

2. Create a new database

   CREATE DATABASE your_service_name;

3. Enable required extensions

   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   CREATE EXTENSION IF NOT EXISTS "pgcrypto";

4. Run the SQL scripts in order

   # For Digital Comic Business (example)
   psql -d your_database -f databases/digital_comic_business/1_TABLES.sql
   psql -d your_database -f databases/digital_comic_business/2_VIEWS.sql
   psql -d your_database -f databases/digital_comic_business/3_INDEXES.sql
   psql -d your_database -f databases/digital_comic_business/4_FUNCTIONS.sql
   psql -d your_database -f databases/digital_comic_business/5_PROCEDURES.sql
   psql -d your_database -f databases/digital_comic_business/6_TRIGGERS.sql

Quick Start Example

-- Connect to your database
\c your_database

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Run all scripts for Digital Comic Business
\i databases/digital_comic_business/1_TABLES.sql
\i databases/digital_comic_business/2_VIEWS.sql
\i databases/digital_comic_business/3_INDEXES.sql
\i databases/digital_comic_business/4_FUNCTIONS.sql
\i databases/digital_comic_business/5_PROCEDURES.sql
\i databases/digital_comic_business/6_TRIGGERS.sql

📋 Features

Standardized Architecture

Each database implementation follows a consistent architecture:

• Core schemas - Standard schemas (core, legal, audit) for common functionality
• Business-specific schemas - Domain-specific schemas tailored to each business type
• UUID primary keys - Scalable and secure identifier system
• Comprehensive indexing - Optimized for common query patterns
• Audit trails - Complete activity logging and change tracking
• Security measures - Password hashing, access controls, and data protection

Common Schemas

All implementations include these core schemas:

• core - User management, authentication, and roles
• legal - Document management and compliance
• audit - Activity logging and audit trails

🔧 Customization

Adapting for Your Business

1. Review the existing implementation that best matches your needs
2. Modify the schema structure to fit your specific requirements
3. Update table names and relationships to match your domain
4. Customize business logic in functions and procedures
5. Adjust security policies for your specific use case

Adding New Services

To add a new database implementation:

1. Create a new directory in databases/ with descriptive name
2. Follow the file naming convention (1_TABLES.sql, 2_VIEWS.sql, etc.)
3. Implement the standard schemas (core, content, commerce, etc.)
4. Add comprehensive documentation (README_en.md, README_vi.md)
5. Create database diagrams (DIAGRAM.md, DIAGRAM.svg)
6. Update this README with the new service information

📚 Documentation

For Each Database Implementation

• README_en.md - English documentation with setup instructions
• README_vi.md - Vietnamese documentation (optional)
• DIAGRAM.md - Text-based database schema diagram
• DIAGRAM.png - Visual database schema diagram

Repository Documentation

• documents/convention.md - Coding standards and conventions
• README.md - This overview file

🤝 Contributing

We welcome contributions to expand the database library! Here's how you can help:

Adding New Database Implementations

1. Choose a business domain that's not yet covered
2. Follow the established conventions (see documents/convention.md)
3. Create comprehensive documentation in both English and Vietnamese
4. Include database diagrams for visual understanding (using mermaid graph or export diagram via Postgres's ERD tool)
5. Test with sample data and common use cases

Improving Existing Implementations

1. Identify areas for improvement (performance, security, features)
2. Follow the existing patterns and conventions
3. Update documentation to reflect changes
4. Test thoroughly to ensure no regressions

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

---

Note: This repository is designed to be a living library that grows with the needs of the development community. Each implementation is thoroughly tested and follows industry best practices for database design and security.