# database-schema

> Designs and maintains MariaDB tables for game state and player data.
Use when: creating new tables, modifying schemas, writing SQL migrations, debugging data integrity issues, optimizing queries, or adding custom feature persistence.

- Author: valeriybaranyshyn-pixel
- Repository: valeriybaranyshyn-pixel/Tales-of-Aden
- Version: 20260202145225
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/valeriybaranyshyn-pixel/Tales-of-Aden
- Web: https://mule.run/skillshub/@@valeriybaranyshyn-pixel/Tales-of-Aden~database-schema:20260202145225

---

---
name: database-schema
description: |
  Designs and maintains MariaDB tables for game state and player data.
  Use when: creating new tables, modifying schemas, writing SQL migrations, debugging data integrity issues, optimizing queries, or adding custom feature persistence.
allowed-tools: Read, Edit, Write, Glob, Grep, Bash
---

# Database Schema Skill

Manages MariaDB database schemas for the L2J Tales of Aden server. The schema files live in `Files/tools/sql/` with ~90 tables covering player data, game state, events, and custom features. All database operations use the MariaDB Java client via JDBC connections defined in server.properties.

## Quick Start

### Core Tables Structure

```sql
-- Character data (Files/tools/sql/characters.sql)
CREATE TABLE `characters` (
  `obj_Id` INT NOT NULL DEFAULT 0,
  `account_name` VARCHAR(45) DEFAULT NULL,
  `char_name` VARCHAR(35) NOT NULL,
  `level` TINYINT UNSIGNED DEFAULT NULL,
  `x` INT DEFAULT NULL,
  `y` INT DEFAULT NULL,
  `z` INT DEFAULT NULL,
  PRIMARY KEY (`obj_Id`),
  KEY `account_name` (`account_name`),
  KEY `char_name` (`char_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

### Adding a Custom Feature Table

```sql
-- Files/tools/sql/custom_feature.sql
CREATE TABLE IF NOT EXISTS `custom_feature` (
  `char_id` INT NOT NULL,
  `feature_data` TEXT,
  `last_used` BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`char_id`),
  CONSTRAINT `fk_custom_char` FOREIGN KEY (`char_id`) 
    REFERENCES `characters`(`obj_Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

## Key Concepts

| Concept | Usage | Example |
|---------|-------|---------|
| obj_Id | Primary key for game objects | `characters.obj_Id`, `items.object_id` |
| char_id | Foreign key to characters | `clan_data.leader_id` |
| BIGINT timestamps | Java System.currentTimeMillis() | `last_login BIGINT NOT NULL` |
| TEXT for serialized data | Skill lists, item attributes | `skills TEXT` |
| InnoDB engine | Required for FK constraints | `ENGINE=InnoDB` |

## Common Patterns

### Player-Owned Data Table

**When:** Adding any feature that stores per-player data.

```sql
CREATE TABLE IF NOT EXISTS `player_feature` (
  `char_id` INT NOT NULL,
  `value` INT NOT NULL DEFAULT 0,
  `updated_at` BIGINT NOT NULL,
  PRIMARY KEY (`char_id`),
  CONSTRAINT `fk_player_feature` FOREIGN KEY (`char_id`)
    REFERENCES `characters`(`obj_Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

### Event Participation Tracking

**When:** Adding PvP events that need history tracking.

```sql
CREATE TABLE IF NOT EXISTS `event_history` (
  `id` INT AUTO_INCREMENT,
  `char_id` INT NOT NULL,
  `event_type` VARCHAR(20) NOT NULL,
  `result` TINYINT NOT NULL,  -- 0=loss, 1=win
  `timestamp` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_char_event` (`char_id`, `event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

## See Also

- [patterns](references/patterns.md)
- [workflows](references/workflows.md)

## Related Skills

- See the **mariadb** skill for connection configuration and Java client usage
- See the **l2j-gameserver** skill for DAO patterns and data loading
- See the **configuration-management** skill for database connection properties