Sql & Veritabanı İle Veri Odaklı Dinamik Puanlama Sistemi Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Bu sistemi kurmak için temel düzeyde SQL bilgisine ve bir veritabanı yönetim sistemine (PostgreSQL veya MySQL 8.0+) ihtiyacınız vardır. 2026 yılı itibarıyla performans için PostgreSQL'in sunduğu "Generated Columns" (Hesaplanmış Sütunlar) veya "Triggers" (Tetikleyiciler) özelliklerini kullanacağız.

• Veritabanı: PostgreSQL 16+ veya MySQL 8.4+
• Sunucu tarafı dil: PHP 8.3 veya Node.js 22 (Örneklerde SQL mantığına odaklanacağız)
• Araçlar: DBeaver veya pgAdmin gibi bir veritabanı yönetim arayüzü

Adım 1: Veri Modeli ve Tablo Tasarımı

Dinamik puanlama sisteminin temeli, eylemlerin kaydedildiği bir "log" tablosu ve bu eylemlerin toplam puanını tutan bir "summary" tablosudur. Veri bütünlüğünü sağlamak için normalizasyon kurallarına uymalıyız.

CREATE TABLE user_actions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    points_earned INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_scores (
    user_id INT PRIMARY KEY,
    total_score INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Burada user_actions tablosu tüm hareketleri tutarken, user_scores tablosu performans için önceden hesaplanmış (denormalize edilmiş) toplam puanı saklar.

Adım 2: Dinamik Puanlama İçin Tetikleyici (Trigger) Oluşturma

Puanlama sisteminin dinamik olması, her yeni eylemde puanın otomatik güncellenmesini gerektirir. Uygulama kodunda her seferinde "topla ve güncelle" yapmak yerine, veritabanı seviyesinde bir tetikleyici kullanmak hem daha hızlıdır hem de veri tutarlılığını garanti eder.

CREATE OR REPLACE FUNCTION update_user_score()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_scores (user_id, total_score)
    VALUES (NEW.user_id, NEW.points_earned)
    ON CONFLICT (user_id) 
    DO UPDATE SET 
        total_score = user_scores.total_score + EXCLUDED.total_score,
        last_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_after_action
AFTER INSERT ON user_actions
FOR EACH ROW EXECUTE FUNCTION update_user_score();

Bu kod, user_actions tablosuna her yeni satır eklendiğinde çalışır. ON CONFLICT ifadesi, kullanıcının daha önce puanı varsa güncelleme yapar, yoksa yeni kayıt oluşturur.

Adım 3: Puan Katsayıları ve İş Mantığı

Puanlama sisteminizi esnek tutmak için puan değerlerini sabit kodlamak yerine bir ayar tablosunda tutmalısınız. Bu sayede kod değiştirmeden puanlama kurallarını güncelleyebilirsiniz.

CREATE TABLE action_rules (
    action_key VARCHAR(50) PRIMARY KEY,
    points_value INT NOT NULL
);

INSERT INTO action_rules (action_key, points_value) VALUES 
('login', 5),
('purchase', 100),
('comment', 10);

Bu tablo sayesinde, "yorum yapma" puanını 10'dan 15'e çıkarmak istediğinizde sadece SQL tablosunu güncellemeniz yeterlidir.

Adım 4: Güvenli Veri Girişi ve SQL Injection Koruması

Veri odaklı sistemlerde en büyük risk SQL Injection saldırılarıdır. Kullanıcıdan gelen veriyi asla doğrudan sorguya eklemeyin. Her zaman "Prepared Statements" (Hazırlanmış İfadeler) kullanın.

// PHP PDO Örneği
$stmt = $pdo->prepare("INSERT INTO user_actions (user_id, action_type, points_earned) VALUES (?, ?, ?)");
$stmt->execute([$userId, $actionType, $points]);

Kritik Güvenlik Uyarısı: Veritabanı işlemlerinde asla kullanıcıdan gelen veriyi string birleştirme (concatenation) yöntemiyle sorguya dahil etmeyin. Her zaman parametreli sorgular kullanarak SQL Injection riskini ortadan kaldırın.

Adım 5: Performans İçin İndeksleme Stratejileri

Sistem büyüdükçe, milyonlarca satır arasında toplam puan sorgusu yapmak yavaşlayacaktır. user_id sütunlarına mutlaka indeks eklemelisiniz.

CREATE INDEX idx_user_actions_user_id ON user_actions(user_id);
CREATE INDEX idx_user_scores_total_score ON user_scores(total_score DESC);

İndeksler, veritabanının veriyi taramak yerine doğrudan adrese gitmesini sağlar. total_score üzerindeki indeks, liderlik tablosu (leaderboard) sorgularını milisaniyeler seviyesine indirir.

Adım 6: Liderlik Tablosu Sorgulama

Sistemin en popüler özelliği olan "En Yüksek Puanlı 10 Kullanıcı" listesini almak oldukça basittir. İndeksli tablomuz sayesinde bu sorgu çok hızlı çalışacaktır.

SELECT user_id, total_score 
FROM user_scores 
ORDER BY total_score DESC 
LIMIT 10;

Bu sorgu, gerçek zamanlı olarak en aktif kullanıcılarınızı listeler.

Yöntem Karşılaştırması

Sıkça Sorulan Sorular

Puanlama sisteminde "Race Condition" (Yarış Durumu) nasıl engellenir?

Veritabanı seviyesinde UPDATE sorgusunda atomik işlemler kullanarak (total_score = total_score + X) veya veritabanı kilitleri (row-level locking) ile aynı anda gelen isteklerde veri kaybını önleyebilirsiniz.

Puanlar neden ayrı bir tabloda tutuluyor?

Tüm log tablosunu her seferinde SUM() ile toplamak, kullanıcı sayısı arttıkça sistemi yavaşlatır. Özet tablo, performans için "denormalizasyon" prensibine göre tasarlanmıştır.

Geçmişe dönük puan düzeltmesi nasıl yapılır?

Düzeltme için user_actions tablosuna negatif veya pozitif "düzeltme" (correction) tipinde yeni bir satır ekleyerek tetikleyicinin otomatik olarak toplamı güncellemesini sağlayabilirsiniz.

Veritabanı tablosu çok büyürse ne yapmalıyım?

Veri bölümleme (partitioning) yöntemini kullanarak verileri aylara veya yıllara göre bölebilir, eski verileri arşiv tablolarına taşıyabilirsiniz.

Hangi veritabanı sistemi daha uygun?

Eğer çok karmaşık analitik sorgular yapacaksanız PostgreSQL, daha basit ve hızlı okuma odaklı bir yapı kuracaksanız MySQL tercih edebilirsiniz.

Sorumluluk Reddi: Bu rehberdeki kod örnekleri eğitim amaçlıdır. Üretim ortamına almadan önce veritabanı yedeklerinizi almalı, güvenlik testlerini yapmalı ve yük testi (load testing) gerçekleştirmelisiniz.

İleri Seviye Puanlama: Zaman Bazlı Çürüme (Decay) Mekanizması

Dinamik puanlama sistemlerinde, kullanıcıların sadece geçmişte kazandıkları puanlara güvenerek liderlik tablosunda kalıcı olmaları rekabeti öldürebilir. "Puan Çürümesi" (Score Decay), aktif olmayan kullanıcıların puanlarının zamanla azalmasını sağlayan, oyuncu tutundurma stratejilerinde sıkça kullanılan bir yöntemdir. Bu mantığı SQL seviyesinde bir Event Scheduler ile otomatize edebiliriz.

Aşağıdaki örnek, son 30 gün içinde işlem yapmayan kullanıcıların puanlarını günlük olarak %1 oranında azaltan bir SQL prosedürünü göstermektedir:

DELIMITER //
CREATE PROCEDURE ApplyScoreDecay()
BEGIN
    UPDATE user_scores
    SET score = score * 0.99
    WHERE last_activity_date < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND score > 0;
END //
DELIMITER ;

-- Bu prosedürü her gün gece yarısı çalıştırmak için:
CREATE EVENT daily_score_decay
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY)
DO CALL ApplyScoreDecay();

Puanlama Sistemlerinde Hata Ayıklama ve Veri Bütünlüğü Kontrolü

Dinamik sistemlerde en büyük risk, tetikleyicilerin (triggers) veya uygulama katmanındaki hataların puan tablosu ile işlem geçmişi (transaction log) arasındaki tutarsızlığı bozmasıdır. Veri bütünlüğünü sağlamak için periyodik olarak "mutabakat" (reconciliation) sorguları çalıştırmalısınız.

Aşağıdaki sorgu, user_scores tablosundaki toplam puan ile score_transactions tablosundaki toplam puanı karşılaştırarak tutarsızlık olan kullanıcıları tespit eder:

SELECT 
    s.user_id, 
    s.score AS current_score, 
    SUM(t.amount) AS calculated_score
FROM user_scores s
JOIN score_transactions t ON s.user_id = t.user_id
GROUP BY s.user_id
HAVING s.score != SUM(t.amount);

Veri Bütünlüğü İçin İpuçları

• İşlem Günlüğü (Audit Log): Her puan değişikliğini mutlaka bir score_transactions tablosunda tutun. Asla doğrudan user_scores tablosunu güncelleyip eski değeri silmeyin.
• Soft Delete Kullanımı: Kullanıcı silme işlemlerinde puanları tamamen yok etmek yerine, veritabanı bütünlüğünü bozmamak için is_active bayrağı kullanın.
• İşlem (Transaction) Blokları: Puan güncellemelerini mutlaka START TRANSACTION ve COMMIT blokları içerisinde gerçekleştirin.

START TRANSACTION;
-- Puan işlemini kaydet
INSERT INTO score_transactions (user_id, amount, reason) VALUES (101, 50, 'bonus');
-- Toplam puanı güncelle
UPDATE user_scores SET score = score + 50 WHERE user_id = 101;
COMMIT;

Bu yöntemle, sisteminizde bir hata oluştuğunda ROLLBACK komutu ile veritabanını tutarlı bir duruma geri döndürebilir, puan kayıplarının önüne geçebilirsiniz.

Sonuç

Sql & veritabanı ile veri odaklı dinamik puanlama sistemi kurmak, veritabanı mimarisini doğru kurgulamaktan geçer. Tetikleyiciler, indeksleme ve doğru tablo tasarımı ile milyonlarca kullanıcıya hizmet verebilecek ölçeklenebilir bir yapı oluşturabilirsiniz. Bir sonraki adım olarak, bu puanlama sistemine "seviye atlama" (leveling) mantığını ekleyerek kullanıcı deneyimini daha da zenginleştirebilirsiniz.