SQL[ite] Table Specifications

Account Information Table

create table tmw_accounts (
    -- Username
    user varchar(32) unique primary key not null,
    -- Password hash
    password varchar(32) not null,
    -- Email address
    email varchar(128) unique not null
);

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;

• List player inventory

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';