SQL table specifications
Account Information Table
tmw_accounts
|
Column name |
Datatype |
Nullable |
References |
Description
|
id
|
integer unsigned primary key auto_increment
|
Unique account ID
|
{{{4}}}
|
{{{5}}}
|
username
|
varchar(32) not null unique
|
User login name
|
{{{4}}}
|
{{{5}}}
|
password
|
varchar(32) not null
|
Password hash
|
{{{4}}}
|
{{{5}}}
|
email
|
varchar(64)
|
User email
|
{{{4}}}
|
{{{5}}}
|
level
|
tinyint unsigned not null
|
User level (restricted, normal, moderator, admin)
|
{{{4}}}
|
{{{5}}}
|
banned
|
mediumint unsigned not null
|
UNIX time when ban is over
|
{{{4}}}
|
{{{5}}}
|
|
Character/Player Information Table
create table tmw_characters (
-- [Unique] character name
name varchar(32) unique primary key not null,
-- Username
user varchar(32) not null,
-- Player information
gender int not null,
level int not null,
money int not null,
-- Coordinates & map
x int not null,
y int not null,
map text not null,
-- Statistics
strength int not null,
agility int not null,
vitality int not null,
intelligence int not null,
dexterity int not null,
luck int not null,
-- Player equipment
--inventory blob not null,
--equipment blob not null,
-- Table relationship
foreign key(user) references tmw_accounts(user)
);
Item type table
create table tmw_base_items (
-- Name of item
name varchar(32) unique primary key not null,
-- Item attributes here
weight int not null
);
In-game object table
create table tmw_items (
id int unique primary key not null,
name varchar(32) not null,
-- Special (unique) item attribues
-- Table relationship
foreign key (name) references tmw_base_items(name)
);
Character inventory table
create table tmw_inventory (
-- Item
id int not null,
-- Character who has the item
name varchar(32) not null,
-- Table relationship
foreign key(id) references tmw_items(id),
foreign key(name) references tmw_characters(name)
);
Character equipment table (items which are equipped)
create table tmw_equipment (
-- Item
id int not null,
-- Character who has item
name varchar(32) not null,
-- Item slot equipped on
slot int not null,
-- Table relationship
foreign key(id) references tmw_items(id),
foreign key(name) references tmw_characters(name)
);
Example queries
- Count total number of in-game objects
select count(*) from tmw_items;
- Count total number of item types
select count(*) from tmw_base_items;
select distinct tmw_items.id, tmw_items.name
from tmw_items, tmw_inventory
where tmw_items.id = tmw_inventory.id
and tmw_inventory.name = 'nym the great';
- Find weight of player inventory
select sum(tmw_base_items.weight)
from tmw_base_items, tmw_items, tmw_inventory
where tmw_base_items.name = tmw_items.name
and tmw_items.id = tmw_inventory.id
and tmw_inventory.name = 'nym the great';