This all looks like a pretty good start for a small RPG. Here's some suggestions.
users.user_id should be an integer. Names change, but IDs should not. Add a name column. In general, primary keys for data tables (distinct from join tables) should be simple integers.
Consider user_equipment. Having a bunch of identical columns is a red flag.
While you have a fixed number of inventory slots, consider how you'd search if a player has a given item equipped.
select 1
from user_equipment
where user_id = :user_id
and :item_id in (head_id, body_id, hand_id, feet_id, main_hand_id, off_hand_id)
When you need to add or remove a new slot, the (increasingly large) table must be altered, and all the queries, too. And you need six indexes.
Instead, have a table to store the types of slots, and whatever other information you need. Then a table to store what's in each slot for each user. This simplifies indexing and searching.
create slots (
-- You can also use an integer.
-- I chose char(2) because it is easier to know what it is without a join.
slot_id char(2) primary key,
name text not null
);
insert into slots values
('H', 'Head'), ('B', 'Body'), ('MH', 'Main Hand'),
('OH', 'Off Hand'), ('F', 'Feet');
create user_slots (
user_id integer not null references users(id),
item_id integer not null references items(id),
slot_id char(2) not null references slots(slot_id),
-- Enforce only one item per user slot
unique(user_id, slot_id)
)
Now querying if a user has an item equipped is easy.
select 1
from user_slots
where user_id = :user_id
and item_id = :item_id
Or all of a user's equipment, and what slot its in.
select slots.name, item_id
from user_slots us
join slots on us.slot_id = slots.slot_id
where user_id = :user_id
Or you can find who has an item equipped and in what slot.
select user_id, item_id, slots.name
from user_slots us
join slots on us.slot_id = slots.slot_id
where item_id = :item_id
Indexing is important to keep performance the same as the tables grow. For example, all the foreign keys are indexed. If you search by user_id or item_id SQLite won't have to search the whole table. But if you search by items.name, which has no indexes, it will have to search the whole items table.
Indexing is a very big topic. You have to think about how tables will grow and how they will be searched. For example, users will grow unbounded. items will likely only get so large. user_equipped will grow as users grow, but an individual user will only have so many items; since user_id and item_id are indexed you're likely to be fine.
For example, checking if a user has an item equipped will perform well because all the foreign keys are indexed.
select user_id, item_id, slots.name
from user_slots us
join slots on us.slot_id = slots.slot_id
where item_id = :item_id
But looking for users who haven't been checked in a certain time will have to search the whole table unless users.last_checked is indexed.
select user_id
from users
where last_checked < :time
Similarly, indexes are also used for ordering. This query will perform poorly unless users.last_checked is indexed.
select user_id
from users
order by last_checked desc
(And consider whether last_checked should be a
Unless there's a good reason to separate it, user_stats should be merged into users.
users
user_id TEXT NOT NULL,
attack INTEGER DEFAULT 100,
defense INTEGER DEFAULT 100,
perception INTEGER DEFAULT 5,
spirit_stones INTEGER DEFAULT 0,
cultivation_val INTEGER DEFAULT 0,
last_checked DATE NOT NULL,
PRIMARY KEY(user_id)
If there is a good reason to separate it, put all the user stats in the same place.
user_stats
user_id TEXT NOT NULL,
attack INTEGER DEFAULT 100,
defense INTEGER DEFAULT 100,
perception INTEGER DEFAULT 5,
spirit_stones INTEGER DEFAULT 0,
cultivation_val INTEGER DEFAULT 0,
FOREIGN KEY(user_id) REFERENCES users(user_id),
PRIMARY KEY(user_id)
users - Stores all users.
user_id TEXT NOT NULL,
last_checked DATE NOT NULL,
PRIMARY KEY(user_id)
One reason to separate user_stats is you're going to be updating the stats a lot. This might involve row locking. Putting the stats in a separate table avoids locking the vital users table.
