1. Introduction

A database standards document outlines the essential guidelines, protocols, and best practices that an organization should follow to ensure consistency, quality, and maintainability across its databases. This document is typically used by database administrators, developers, data architects, and other stakeholders involved in database management and development. Here’s a suggested structure and description for your document:

2. Purpose

This document serves as the official standard for database design, development, and management within SIONIQ. Its purpose is to establish uniform guidelines that enhance data integrity, promote best practices, and ensure efficiency and interoperability across all database systems. Following these standards helps improve data consistency, system reliability, and security.

3. Scope

This document applies to all relational database management systems (RDBMS) and any databases developed, maintained, or administered by SIONIQ. This includes but is not limited to the development, deployment, maintenance, security, and governance of databases. The primary focus will be on SQL Server 2022, though principles can be adapted for other platforms if necessary.

4. Table Naming Conventions

Consistent naming conventions help maintain clarity and organization within SQL databases. Follow these standards for naming various database objects:

To maintain consistency and readability, follow these guidelines when naming tables:

4.1 General Instructions

To ensure clarity, consistency, and maintainability in the database structure, follow these general table naming conventions:

• Use Descriptive Names: Choose names that clearly describe the content or purpose of the table (e.g., Employees, SalesOrders).
• Plural Naming: Use plural nouns to indicate that the table holds multiple records of the entity (e.g., Customers rather than Customer).
• Avoid Spaces and Special Characters: Do not use spaces,underscores, hyphens, or special characters in table names. Use underscores (_) to history tables (e.g., OrderDetails_History).
• Prefix with Module Short Codes: If tables are grouped by modules, use a standard prefix for each module (e.g., adm for Admin, inv for Inventory) followed by the table name. This helps identify the module association.
• Avoid Abbreviations: Use full words whenever possible for readability. Avoid using unclear abbreviations unless they are widely understood.

By following these guidelines, tables will be named in a way that is logical, easy to understand, and scalable as the database grows.

4.2 Use PascalCase

PascalCase is a naming convention in which each word in a compound word or phrase begins with a capital letter. It is commonly used in programming and database design for naming classes, methods, and variables.

Examples of PascalCase

• Single Word: PascalCase
• Multiple Words:
  • InventoryOrder
  • CustomerDetails
  • GetCustomerInfo
  • EmployeeRecord

Key Characteristics

• First Letter Capitalized: Each word starts with a capital letter.
• No Spaces or Underscores: There are no separators between words.
• Readability: Capital letters enhance readability and understanding.

Usage

PascalCase is often used in the following contexts:

• Programming: Common for class names, method names, and some variable names, especially in languages like C# and Java.
• Database Objects: Applied to table names, column names, and stored procedures for consistency and clarity.

PascalCase is beneficial for creating clear and descriptive identifiers, enhancing the readability of code and database structures.

4.3 Module Prefixes

To categorize tables by their respective modules, use a short code prefix for each module in lowercase, followed by the table name. This helps to quickly identify the module a table belongs to.

Guidelines:

• Module Prefix: Use lowercase module short codes as prefixes (e.g., adm for Admin, inv for Inventory).
• Table Naming Structure: Format table names as prefixTableName (e.g., invStock, admCountry).
• Example: Use posBilling for the Billing table in the POS module.

Module Prefix List:

4.4 Parent-Child Table Naming Convention

For tables with associated child tables, use a module short code as a prefix, followed by the table name. Append the suffix HDR for the parent table and DTL for child tables. This convention indicates both the module and the hierarchical relationship between tables.

Guidelines:

• Module Prefix: Use lowercase module short codes as prefixes (e.g., adm for Admin, inv for Inventory).
• Parent Table (Header): Append the suffix HDR to the parent table name after the module prefix (e.g., invOrderHDR, admInvoiceHDR).
• Child Table (Detail): Append the suffix DTL to the child table name after the module prefix, which contains related detailed information (e.g., invOrderDTL, admInvoiceDTL).
• Consistency: Use this naming convention for all module-based parent-child structures to maintain clarity and consistency across the database.

Example:

Following this convention clarifies both the module and the hierarchical relationship between tables, making it easier to manage and understand the database structure.

4.5 Vertical Table Naming Convention

For vertical tables that include parent-child relationships, use a module short code as a prefix, followed by the table name. Append the suffix VRL to both parent and child tables to indicate that they are part of a vertical structure.

Guidelines:

• Module Prefix: Use lowercase module short codes as prefixes (e.g., adm for Admin, inv for Inventory).
• Vertical Table (Parent): Append the suffix VRL to the parent table name after the module prefix (e.g., invOrderVRL, admInvoiceVRL).
• Vertical Table (Child): Append the suffix VRL to the child table name as well, typically representing detailed records (e.g., invOrderItemsVRL, admInvoiceDetailsVRL).
• Consistency: Use this naming convention across all module-based vertical table structures to ensure consistency and clarity in the database.
• Primary Key: Main table primary key and vertical table primary column should be same. Name and Data.

Example:

Using the VRL suffix for both parent and child tables in a vertical structure makes it clear that they belong to a specific hierarchy within each module.

4.6 History Table Naming Convention

For tables with historical data, use a module short code as a prefix, followed by the table name. Append the suffix _History to all table types to indicate that they store historical records. This includes parent and child tables in a vertical structure.

Guidelines:

• Module Prefix: Use lowercase module short codes as prefixes (e.g., adm for Admin, inv for Inventory).
• Vertical Table (Parent): Append the suffix VRL to the parent table name after the module prefix (e.g., invOrderVRL, admInvoiceVRL).
• Vertical Table (Child): Append the suffix VRL to the child table name as well (e.g., invOrderItemsVRL, admInvoiceDetailsVRL).
• History Tables: For all tables, append the suffix _History to indicate that they contain historical data (e.g., invOrderHistory, admInvoiceHistory).
• Column Naming Convention: For columns within tables, use the module prefix followed by a descriptive name. For parent table columns, end with HDR, and for child table columns, end with DTL.
• Consistency: Maintain this naming convention across all module-based history and vertical table structures for clarity and consistency.

Example:

By using the _History suffix for all types of tables, along with VRL for vertical tables and HDR / DTL for columns, the structure remains clear and organized within the database.

4.7 Settings Table Name Conventions

Application Settings:

Module Settings:

4.8 Table Name Structure Summary

The structure of a table name can be represented as follows:

This format clearly indicates the purpose of the table, making it easy to understand the module it belongs to and its role within the database.

5. Table Column Naming Conventions

5.1 General Instructions

Follow these guidelines when naming columns:

• Keep names as short as possible while still being descriptive.
• Avoid abbreviations unless they are widely accepted.
• For boolean columns, use a Is or Has prefix: IsActive, HasDiscount.

5.2 Column Name Creation

Column names should be clear, descriptive, and follow the PascalCase naming convention. Use nouns to represent the data stored in the column.

• Use PascalCase for naming: OrderDate, CustomerName.
• Avoid using reserved words or special characters.
• Be consistent in naming across similar types of data.

5.3 Primary Key Columns

Primary key columns should follow these conventions:

• Use the table name followed by ID: OrderID, CustomerID.
• Make sure the primary key is unique and not null.

5.4 Foreign Key Columns

Foreign key columns should clearly indicate their relationship to the primary key of another table:

• Use the name of the referenced table followed by ID: CustomerID in the Order table referencing Customer table.
• CustomerID column primary key the in the Customer table and foreign key in Order table.

5.5 Common Column Specifications

5.6 Summary Table of Column Naming Conventions

6. Stored Procedures Naming Conventions

Common Naming conventions

7. Functions Naming Conventions

Common Naming conventions

8. Data Types

Select appropriate data types for each field:

9. Indexing Guidelines

Best practices for indexing:

• For clustered indexes: IX___CL
• For non-clustered indexes: IX___NC
• For composite indexes: IX____NC