database_design
Schema tasarımı, migration stratejileri, indexing, query optimization ve database best practices.
>_
Quick Installnpxskills add vuralserhat86/antigravity-agentic-skills--skill database_designInstructions
Loading…
Schema tasarımı, migration stratejileri, indexing, query optimization ve database best practices.
npxskills add vuralserhat86/antigravity-agentic-skills--skill database_designLoading…
Schema tasarımı, migration ve query optimization rehberi.
-- Tablolar: snake_case, çoğul
CREATE TABLE users (...);
CREATE TABLE order_items (...);
-- Kolonlar: snake_case
user_id, created_at, is_active
-- Primary Key: id veya table_id
id SERIAL PRIMARY KEY
-- veya
user_id UUID PRIMARY KEY
-- Foreign Key: referenced_table_id
user_id INTEGER REFERENCES users(id)
| Veri | PostgreSQL | MySQL |
|---|---|---|
| ID | UUID / SERIAL | CHAR(36) / INT AUTO_INCREMENT |
| Text (kısa) | VARCHAR(255) | VARCHAR(255) |
| Text (uzun) | TEXT | TEXT |
| Tarih | TIMESTAMP WITH TIME ZONE |
| Form | Kural | Örnek |
|---|---|---|
| 1NF | Atomik değerler | address → street, city, zip |
| 2NF | Tam bağımlılık | Composite key parçalama |
| 3NF | Transitif bağımlılık yok | user.department_name → ayrı tablo |
-- B-Tree (varsayılan, genel amaçlı)
CREATE INDEX idx_users_email ON users(email);
-- Composite Index (sıra önemli!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Partial Index (koşullu)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
-- Unique Index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- GIN/GiST (full-text, JSON, array)
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
✅ Index Ekle:
- WHERE clause'da sık kullanılan kolonlar
- JOIN kolonları (foreign keys)
- ORDER BY kolonları
- Unique constraint gereken kolonlar
❌ Index Ekleme:
- Düşük cardinality (boolean, enum)
- Sık güncellenen kolonlar
- Küçük tablolar (<1000 row)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
AND created_at > '2025-01-01';
-- ❌ YANLIŞ: SELECT *
SELECT * FROM users;
-- ✅ DOĞRU: Sadece gerekli kolonlar
SELECT id, name, email FROM users;
-- ❌ YANLIŞ: N+1 query
FOR user IN users:
SELECT * FROM orders WHERE user_id = user.id
-- ✅ DOĞRU: JOIN veya IN
SELECT * FROM orders WHERE user_id IN (1, 2, 3);
-- ❌ YANLIŞ: Function on indexed column
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- ✅ DOĞRU: Range query
SELECT * FROM users
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
-- Offset-based (küçük veri setleri)
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;
-- Cursor-based (büyük veri setleri, önerilen)
SELECT * FROM users
WHERE id > :last_id
ORDER BY id
LIMIT 20;
migrations/
├── 001_create_users_table.sql
├── 002_add_email_to_users.sql
├── 003_create_orders_table.sql
└── 004_add_index_orders_user_id.sql
-- ✅ Backward compatible
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- ⚠️ Dikkatli ol (default value gerekli)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- ❌ Tehlikeli (prodda direkt yapma)
ALTER TABLE users DROP COLUMN old_field;
DROP TABLE deprecated_table;
// Embedded (1:few, read-heavy)
{
_id: ObjectId("..."),
name: "John",
addresses: [
{ street: "123 Main", city: "NYC" },
{ street: "456 Oak", city: "LA" }
]
}
// Referenced (1:many, write-heavy)
// users collection
{ _id: ObjectId("..."), name: "John" }
// orders collection
{ _id: ObjectId("..."), user_id: ObjectId("..."), total: 100 }
STRING → Cache, session
HASH → Object storage
LIST → Queue, timeline
SET → Tags, unique items
ZSET → Leaderboard, ranking
Database Design v1.1 - Enhanced
Kaynak: System Design Primer - Database
| Aşama | Doğrulama |
|---|---|
| 1 | ER diyagramı tüm use-case'leri kapsıyor mu? |
| 2 | Her tablo için Primary Key var mı? |
| 3 | EXPLAIN ile sorgu maliyetleri kontrol edildi mi? |
DATETIME| Boolean | BOOLEAN | TINYINT(1) |
| JSON | JSONB | JSON |
| Para | DECIMAL(19,4) | DECIMAL(19,4) |
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).