Fiziksel Veri Modeli Şablonu¶

ISO/IEC 15504 SPICE | ENG.5 Tasarım gereksinimlerinin karşılanması beklenmektedir.

1. Veri Modeli Genel Bakış¶

1.1. Fiziksel Veri Modeli Diagramı¶

[ERD (Entity Relationship Diagram) buraya eklenecek]

1.2. Veri Modeli Prensipleri¶

Normalizasyon: [3NF/BCNF seviyesi]
Denormalizasyon: [Performance için gerekli durumlar]
Data Integrity: [Referential integrity, constraints]
Scalability: [Partitioning, sharding strategies]
Performance: [Indexing, query optimization]

1.3. Database Platform¶

DBMS: [PostgreSQL/SQL Server/MySQL/Oracle]
Version: [X.Y.Z]
Encoding: [UTF-8]
Collation: [Turkish_CI_AS/tr_TR.UTF-8]
Time Zone: [UTC]

2. Schema Yapısı¶

2.1. Database Schemas¶

Schema Name	Purpose	Owner	Description
dbo/public	[Main application data]	[app_user]	[Core business tables]
security	[Authentication/Authorization]	[security_user]	[User management tables]
audit	[Audit trail]	[audit_user]	[Change tracking tables]
config	[Configuration data]	[config_user]	[System configuration]
temp	[Temporary data]	[app_user]	[ETL, batch processing]

2.2. Naming Conventions¶

Tables: [PascalCase/snake_case - örn: User, user_profile]
Columns: [camelCase/snake_case - örn: firstName, first_name]
Primary Keys: [ID/Id/table_name_id]
Foreign Keys: [ReferencedTableId/referenced_table_id]
Indexes: [IX_TableName_ColumnName]
Constraints: [CK_TableName_ColumnName]

3. Ana Tablolar (Core Tables)¶

3.1. [Tablo Adı 1] - Örn: Users¶

Column Name	Data Type	Null	Default	Description
Id	[int/bigint]	No	[IDENTITY/SERIAL]	[Primary key]
FirstName	[nvarchar(50)]	No	-	[User first name]
LastName	[nvarchar(50)]	No	-	[User last name]
Email	[nvarchar(255)]	No	-	[Unique email address]
PasswordHash	[nvarchar(255)]	No	-	[Hashed password]
IsActive	[bit/boolean]	No	[1/true]	[Account status]
CreatedDate	[datetime2/timestamp]	No	[GETDATE()/NOW()]	[Record creation date]
CreatedBy	[int]	No	-	[FK to Users.Id]
ModifiedDate	[datetime2/timestamp]	Yes	-	[Last modification date]
ModifiedBy	[int]	Yes	-	[FK to Users.Id]

Constraints: - PK: PK_Users_Id (Id) - UK: UK_Users_Email (Email) - FK: FK_Users_CreatedBy (CreatedBy → Users.Id) - FK: FK_Users_ModifiedBy (ModifiedBy → Users.Id) - CK: CK_Users_Email (Email format validation)

Indexes: - Clustered: PK_Users_Id (Id) - Non-Clustered: IX_Users_Email (Email) - Non-Clustered: IX_Users_IsActive_CreatedDate (IsActive, CreatedDate)

3.2. [Tablo Adı 2] - Örn: Products¶

Column Name	Data Type	Null	Default	Description
Id	[int/bigint]	No	[IDENTITY/SERIAL]	[Primary key]
Name	[nvarchar(100)]	No	-	[Product name]
Description	[nvarchar(500)]	Yes	-	[Product description]
Price	[decimal(18,2)]	No	[0]	[Product price]
CategoryId	[int]	No	-	[FK to Categories.Id]
IsActive	[bit/boolean]	No	[1/true]	[Product status]
CreatedDate	[datetime2/timestamp]	No	[GETDATE()/NOW()]	[Record creation date]
CreatedBy	[int]	No	-	[FK to Users.Id]
ModifiedDate	[datetime2/timestamp]	Yes	-	[Last modification date]
ModifiedBy	[int]	Yes	-	[FK to Users.Id]

Constraints: - PK: PK_Products_Id (Id) - FK: FK_Products_CategoryId (CategoryId → Categories.Id) - FK: FK_Products_CreatedBy (CreatedBy → Users.Id) - FK: FK_Products_ModifiedBy (ModifiedBy → Users.Id) - CK: CK_Products_Price (Price >= 0)

Indexes: - Clustered: PK_Products_Id (Id) - Non-Clustered: IX_Products_CategoryId (CategoryId) - Non-Clustered: IX_Products_Name (Name) - Non-Clustered: IX_Products_IsActive_Price (IsActive, Price)

3.3. [Tablo Adı 3] - [Diğer önemli tablolar için devam edin]¶

4. İlişkiler (Relationships)¶

4.1. Ana İlişkiler¶

[İlişki diagramı buraya eklenecek]

Parent Table	Child Table	Relationship Type	Foreign Key	Description
Users	[Products]	[One-to-Many]	[CreatedBy]	[User creates products]
Categories	[Products]	[One-to-Many]	[CategoryId]	[Category contains products]
Users	[Orders]	[One-to-Many]	[UserId]	[User places orders]
Orders	[OrderItems]	[One-to-Many]	[OrderId]	[Order contains items]
Products	[OrderItems]	[One-to-Many]	[ProductId]	[Product in order items]

4.2. Referential Integrity¶

Cascade Delete: [Hangi ilişkilerde cascade delete kullanılacak]
Restrict Delete: [Hangi ilişkilerde delete kısıtlanacak]
Set Null: [Hangi ilişkilerde null set edilecek]

5. İndeksler ve Performans¶

5.1. Primary Keys¶

Table	Primary Key	Type	Description
Users	[Id]	[Clustered]	[Auto-increment integer]
Products	[Id]	[Clustered]	[Auto-increment integer]
Categories	[Id]	[Clustered]	[Auto-increment integer]

5.2. Unique Indexes¶

Table	Columns	Index Name	Purpose
Users	[Email]	[UK_Users_Email]	[Unique email constraint]
Categories	[Name]	[UK_Categories_Name]	[Unique category name]
Products	[Name, CategoryId]	[UK_Products_Name_Category]	[Unique product per category]

5.3. Composite Indexes¶

Table	Columns	Index Name	Usage Pattern
Products	[IsActive, Price]	[IX_Products_Active_Price]	[Active products by price]
Orders	[UserId, CreatedDate]	[IX_Orders_User_Date]	[User order history]
OrderItems	[OrderId, ProductId]	[IX_OrderItems_Order_Product]	[Order line items]

5.4. Full-Text Indexes (Eğer varsa)¶

Table	Columns	Catalog	Purpose
Products	[Name, Description]	[ProductCatalog]	[Product search]
Users	[FirstName, LastName]	[UserCatalog]	[User search]

6. Veri Tipleri ve Kısıtlamalar¶

6.1. Standart Veri Tipleri¶

Business Type	SQL Server	PostgreSQL	MySQL	Description
ID	[bigint]	[bigserial]	[bigint]	[Primary key]
Short Text	[nvarchar(255)]	[varchar(255)]	[varchar(255)]	[Names, emails]
Long Text	[nvarchar(max)]	[text]	[text]	[Descriptions]
Money	[decimal(18,2)]	[decimal(18,2)]	[decimal(18,2)]	[Currency values]
Date	[date]	[date]	[date]	[Date only]
DateTime	[datetime2]	[timestamp]	[datetime]	[Date and time]
Boolean	[bit]	[boolean]	[boolean]	[True/false values]

6.2. Check Constraints¶

Table	Column	Constraint	Rule
Users	[Email]	[CK_Users_Email]	[Email format validation]
Products	[Price]	[CK_Products_Price]	[Price >= 0]
Orders	[Status]	[CK_Orders_Status]	[Status IN ('Pending', 'Shipped', 'Delivered')]

6.3. Default Values¶

Table	Column	Default Value	Description
Users	[IsActive]	[1/true]	[New users are active]
Products	[CreatedDate]	[GETDATE()/NOW()]	[Current timestamp]
Orders	[Status]	['Pending']	[Default order status]

7. Partitioning ve Sharding (Eğer Gerekirse)¶

7.1. Table Partitioning¶

Table	Partition Key	Partition Type	Strategy
Orders	[CreatedDate]	[Range]	[Monthly partitions]
AuditLogs	[LogDate]	[Range]	[Weekly partitions]
UserSessions	[UserId]	[Hash]	[4 partitions]

7.2. Database Sharding¶

Shard Key: [UserId/TenantId]
Shard Count: [4 shards initially]
Shard Distribution: [Hash-based/Range-based]
Cross-Shard Queries: [Application layer handling]

8. Güvenlik ve Erişim Kontrolü¶

8.1. Database Users ve Roller¶

User/Role	Purpose	Permissions	Tables
app_user	[Application connection]	[SELECT, INSERT, UPDATE, DELETE]	[All application tables]
readonly_user	[Reporting/BI]	[SELECT]	[All tables]
migration_user	[Schema changes]	[DDL, DML]	[All tables]
backup_user	[Backup operations]	[BACKUP]	[Database level]

8.2. Row Level Security (Eğer Gerekirse)¶

Table	Policy Name	Condition	Description
Orders	[UserOrders]	[UserId = CURRENT_USER_ID()]	[Users see only their orders]
Documents	[TenantDocuments]	[TenantId = CURRENT_TENANT_ID()]	[Multi-tenant isolation]

8.3. Sensitive Data Protection¶

Encryption at Rest: [Transparent Data Encryption (TDE)]
Column Encryption: [Always Encrypted for PII data]
Masked Data: [Dynamic data masking for non-prod]
Audit Trail: [Database audit for sensitive tables]

9. Backup ve Recovery¶

9.1. Backup Strategy¶

Backup Type	Frequency	Retention	Recovery Point
Full Backup	[Weekly]	[1 month]	[Weekly point]
Differential	[Daily]	[1 week]	[Daily point]
Transaction Log	[15 minutes]	[1 week]	[15 min point]

9.2. Recovery Scenarios¶

Point-in-Time Recovery: [Any point within 1 week]
Table Recovery: [Individual table restore from backup]
Disaster Recovery: [Cross-region backup restoration]
Testing: [Monthly restore testing]

10. Monitoring ve Maintenance¶

10.1. Performance Monitoring¶

Metric	Threshold	Alert	Action
CPU Usage	[>80%]	[Warning]	[Query optimization]
Memory Usage	[>85%]	[Critical]	[Add memory]
Disk Space	[>90%]	[Critical]	[Add storage]
Slow Queries	[>5 seconds]	[Warning]	[Index optimization]

10.2. Maintenance Tasks¶

Index Maintenance: [Weekly rebuild/reorganize]
Statistics Update: [Daily automatic update]
Integrity Checks: [Weekly DBCC CHECKDB]
Cleanup Tasks: [Old data archival/deletion]

11. Migration ve Deployment¶

11.1. Schema Migration¶

Migration Tool: [Flyway/Liquibase/Entity Framework]
Version Control: [Git-based schema versioning]
Rollback Strategy: [Migration rollback scripts]
Testing: [Migration testing in staging]

11.2. Data Migration (Eğer Geçiş Projesi ise)¶

Source	Target	Migration Method	Validation
Legacy DB	[New Schema]	[ETL Process]	[Row count, checksum]
File System	[Database]	[Bulk Insert]	[Data integrity checks]
External API	[Database]	[Batch Processing]	[Business rule validation]

12. Onay ve Gözden Geçirme¶

12.1. Veri Modeli Gözden Geçirme Kontrol Listesi¶

Normalizasyon kuralları uygulandı mı?
Primary key'ler tanımlandı mı?
Foreign key ilişkileri doğru mu?
Index stratejisi optimize mi?
Veri tipleri uygun seçildi mi?
Güvenlik gereksinimleri karşılandı mı?
Backup stratejisi tanımlandı mı?
Performance hedefleri belirlendi mi?
Migration planı hazırlandı mı?

12.2. Onay Bilgileri¶

Rol	Ad Soyad	Tarih
Database Architect	[Ad Soyad]	[DD.MM.YYYY]
Senior Developer	[Ad Soyad]	[DD.MM.YYYY]
DBA	[Ad Soyad]	[DD.MM.YYYY]
Proje Yöneticisi	[Ad Soyad]	[DD.MM.YYYY]

Not: Bu doküman Proje Dökümantasyon Süreci standartlarına uygun olarak hazırlanmış ve ISO/IEC 15504 SPICE | ENG.5 Tasarım gereksinimlerini karşılamaktadır.