Database Technical Overview

The technical overview for the database currently consists of a set of design specifications. These specifications can be used to infer database behavior.

The DotNetNuke DKP database will consist of 7 tables. These tables are defined as follows:
  • tss_Players: A list of player names and player information.
  • tss_Instances: A list of zones or instances existing within the game.
  • tss_Mobs: A list of mobs belonging to each instance.
  • tss_Items: A list of items that can drop from each mob.
  • tss_Raids: A list of events corresponding to outings involving the dismissal of mobs and consumption of items.
  • tss_Loots: A list of items corresponding to player consumption.
  • tss_Rosters: A list of players attending each raid.

Each table will have a column that serves as a unique identifier for each row in the table. This number is not intended to be made visible to users. This column will be the primary key for each table and will auto-increment.

The following tables will have uniqueness constraints placed on the following fields/groups of fields:
  • tss_Players: PlayerName
  • tss_Instances: InstanceName
  • tss_Mobs: { InstanceID, MobName }
  • tss_Items: ItemName
  • tss_Raids: { MobID, RaidDate }
  • tss_Loots: { RaidID, ItemID, PlayerID }
  • tss_Rosters: { RaidID, PlayerID }

Every column belonging to the above tables will be of type int, datetime, nvarchar, or bit.

Referential integrity is a requirement. The following foreign keys will be in place per table:
  • tss_Players: None
  • tss_Instances: None
  • tss_Mobs: InstanceID depedent on tss_Instances
  • tss_Items: MobID dependent on tss_Mobs
  • tss_Raids: PlayerID dependent on tss_Players, MobID dependent on tss_Mobs
  • tss_Loots: RaidID dependent on tss_Raids, ItemID dependent on tss_Items, PlayerID dependent on tss_Players
  • tss_Rosters: RaidID dependent on tss_Raids, PlayerID dependent on tss_Players

Since the key column is the unique ID for each table, and as this value cannot be changed once assigned, CASCADE UPDATES are irrelevant. The effect of CASCADE DELETES is required; however, as at least one table tss_Loots will have multiple cascade paths from tss_Mobs, this will cause a design inconsistency. In light of this, all CASCADE DELETE effects should be implemented as INSTEAD OF DELETE triggers. The format of these triggers shall be as follows:

ALTER TRIGGER [dbo].[<tablename>_DELETE]
   ON [dbo].[<tablename>]
   INSTEAD OF DELETE
AS
BEGIN

    SET NOCOUNT ON;

    /*
       Delete from dependencies. Repeat for each dependent table.
    */

    DELETE
    FROM <dependent_tablename>
    WHERE <dependent_tablename>.<foreign_key_field>
        IN (SELECT deleted.<foreign_key_field> FROM deleted);

    /*
       Delete from primary table.
    */

    DELETE
    FROM <tablename>
    WHERE <tbalename>.<primary_key_field>
        IN (SELECT deleted.<primary_key_field> FROM deleted);

END


The life-cycle of each record consists of three phases: Active = True, Active = False, and Deleted. The phase of the record is identified by a special column named Active of type bit. If this field is set to True, the record is considered editable and viewable by the user. Once a user "deletes" a record from the interface, the record's Active value is changed to False. When a record is set to Active = False, changes to other fields are not permitted during and after the operation. The record will also no longer appear on the user interface.

Once a record is marked as Active = False, it will remain in the database until another recorded is added or updated in that table. The acts of updating and inserting records will have a DELETE query preceding them which prunes all Active = False records from the table. In this way, "deleted" records persist for trigger needs, while remaining unavailable to the user.

To maintain this system, each table has an INSTEAD OF UPDATE trigger consisting of the following format:

ALTER TRIGGER [dbo].[<tablename>_UPDATE] 
   ON [dbo].[<tablename>]
   INSTEAD OF UPDATE
AS 
BEGIN

    SET NOCOUNT ON;

    /*
       For the records where Active does not change
       and Active = True, update all other fields.
    */
    
    UPDATE <tablename>
    SET <tablename>.<some_field> = checker.new<some_field>
    FROM (
        SELECT inserted.<some_field> AS new<some_field>,
            inserted.<primary_key_field>
        FROM inserted
            INNER JOIN deleted ON inserted.<primary_key_field> = deleted.<primary_key_field>
        WHERE (inserted.Active = 1)
            AND (deleted.Active = 1)) AS checker
    WHERE <tablename>.<primary_key_field> = checker.<primary_key_field>;

    /*
       For the records where Active changes from True to False,
       prevent changes to any other field.
    */

    UPDATE <tablename>
    SET <tablename>.Active = 0
    FROM (
        SELECT inserted.<primary_key_field>
        FROM inserted
            INNER JOIN deleted ON inserted.<primary_key_field> = deleted.<primary_key_field>
        WHERE (inserted.Active = 0)
            AND (deleted.Active = 1)) AS checker
    WHERE <tablename>.<primary_key_field> = checker.<primary_key_field>;

    /*
       Cascade changes to Active.
    */

    UPDATE <dependent_tablename>
    SET <dependent_tablename>.Active = 0
    WHERE <dependent_tablename>.<foreign_key_field> IN (
        SELECT inserted.<primary_key_field>
        FROM inserted
            INNER JOIN deleted ON inserted.<primary_key_field> = deleted.<primary_key_field>
        WHERE (inserted.Active = 0)
            AND (deleted.Active = 1)
    );

END


Keeping in mind that mass-updates may trigger a multi-row inserted or deleted, the first UPDATE retrieves all records which remain Active = True through the update. These records can be found by joining the inserted and deleted tables on the unique identifier and comparing the Active values in the join. The same technique can be used to find the records which are being changed to Active = False in the second UPDATE. Note that in the second update, changes to other fields are ignored. Inactivity also needs to be propagated to as many dependent tables as necessary.

Since no update override exists for records that are Active = False before and after the update, a record that is marked as Active = False will never update to something else. The template above represents a table not involved with DKP accounting, so there is only code for allowing changes to Active = True records and allowing records and their dependencies to be marked as Active = False.

DKP Accounting

So as to provide a scalable environment, DKP accounting is handled by triggers at the point of data modification for Raids, Loots, and Rosters. A few triggers play key roles in accomplishing this task. Here we discuss the triggers involved with DKP accounting.

Gaining DKP through Roster Addition

The simplest way for a player to gain DKP is to be assigned to a raid's roster. When added, the player receives earned and saved DKP equal to the raid's RaidValue.

Trigger Source - Gaining DKP through Roster Addition

Losing DKP through Roster Removal

When a player's roster entry is marked as Active = False, that raid's RaidValue is removed from the player's earned and saved DKP.

Trigger Source - Losing DKP through Roster Removal

Gaining/Losing DKP through Roster Modification

There is a more complicated case of DKP accounting involving roster modifications. Modifications can be of the form...
  • The player specified was the wrong.
  • The raid specified was the wrong.
  • The player and raid specified was wrong.

Trigger Source - Gaining/Losing DKP through Roster Modification

Losing DKP through Loot Addition

When a loot is created for a player, the player gains spent DKP and loses saved DKP.

Trigger Source - Losing DKP through Loot Addition

Gaining DKP through Loot Removal

When a player's loot entry is marked Active = False, that loot's LootValue is returned to the player.

Trigger Source - Gaining DKP through Loot Removal

Gaining/Losing DKP through Loot Modification

Much like raid modifications, loot modifications can result in several scenarios...
  • The player specified was the wrong.
  • The loot value specified was the wrong.
  • The player and loot value specified was wrong.

Trigger Source - Gaining/Losing DKP through Loot Modification

Gaining/Losing DKP through Raid Value Changes

When a raid's RaidValue is changed, negatively or positively, the net difference needs to be added or subtracted from each player listed on that raid's roster.

Trigger Source - Gaining/Losing DKP through Raid Value Changes

Last edited Feb 6, 2008 at 10:49 PM by Salaza, version 19

Comments

No comments yet.