Database schema for storing the inventory item slot-CodePudding

I am working on a small web project where I would like to have an user inventory like in the RPG games or shooting games. I want to keep it as simple as possible. What I want to achieve is to store the slots of the items so that next time when the page is loaded, items would stay on the correct position.Items will have fixed positions such as, one slot for rifle, one slot for headgear.

What kind of relation and db schema should I create for that? I am using MySQL

I have the following schema so far:

CodePudding user response：

I assume some items can occupy one of several slots. I'd create a couple of new tables:

create table slots (
  id int primary key,
  name varchar(20) -- 'headgear', 'rifle', 'coin pocket'
);

create table inventory_slot (
  item_id int not null,
  slot_id int not null,
  primary key (item_id, slot_id),
  foreign key (item_id) references inventory_item(id),
  foreign key (slot_id) references slots(id)
);

There's at least one row in inventory_slot for each possible inventory item, and there may be multiple rows in inventory_slot for the same item, if it is allowed to be held in multiple places on a given user's inventory.

Then modify the user inventory table:

create table user_inventory (
  id int primary key,
  user_id int not null,
  item_id int not null,
  slot_id int not null,
  unique key (user_id, slot_id),
  foreign key (user_id) references user(id),
  foreign key (item_id, slot_id) references inventory_slot(item_id, slot_id)
);

This allows only one item per slot per user because of the unique key. The items must reference a row in inventory_slot so you know they can only be held in slots that have been designated as ok for a given item.