How should I store grid-based character inventories in the database?

Question

I used to encode the whole inventory data into binary but I'm thinking of doing something different now.

The inventory has a 64 slots in an 8×8 grid. Large items like weapons fill up 6 to 8 slots in a vertical rectangle, while potions take 1 slot.

How would you store this data in a database?

Answer 1

I would probably start with the following schema:

Slot (ID, CharacterID, SlotID, ItemID)

where

• Slot is the name of the table
• ID is the table's primary key
• CharacterID is a foreign key that points to the character
• SlotID is the slot's ID going from 1 to 64 (or 0 to 63, or whatever)
• ItemID is a foreign key that points to the item (ID of a specific weapon, potion...)

In case of weapons and other multiple-slots inventory items you'd simply repeat the same ItemID over different SlotID's.

An example of a character that has a "potion of knowledge" at the bottom-right corner of the inventory, and a 6-slot "spear of fire & death" at the top-left corner:

1, 1, 1, 666
2, 1, 2, 0
3, 1, 3, 0
4, 1, 4, 0
5, 1, 5, 0
6, 1, 6, 0
7, 1, 7, 0
8, 1, 8, 0
9, 1, 9, 666
10, 1, 10, 0
11, 1, 11, 0
12, 1, 12, 0
13, 1, 13, 0
14, 1, 14, 0
15, 1, 15, 0
16, 1, 16, 0
17, 1, 17, 666
18, 1, 18, 0
19, 1, 19, 0
20, 1, 20, 0
21, 1, 21, 0
22, 1, 22, 0
23, 1, 23, 0
24, 1, 24, 0
25, 1, 25, 666
26, 1, 26, 0
27, 1, 27, 0
28, 1, 28, 0
29, 1, 29, 0
30, 1, 30, 0
31, 1, 31, 0
32, 1, 32, 0
33, 1, 33, 666
34, 1, 34, 0
35, 1, 35, 0
36, 1, 36, 0
37, 1, 37, 0
38, 1, 38, 0
39, 1, 39, 0
40, 1, 40, 0
41, 1, 41, 666
42, 1, 42, 0
43, 1, 43, 0
44, 1, 44, 0
45, 1, 45, 0
46, 1, 46, 0
47, 1, 47, 0
48, 1, 48, 0
49, 1, 49, 0
50, 1, 50, 0
51, 1, 51, 0
52, 1, 52, 0
53, 1, 53, 0
54, 1, 54, 0
55, 1, 55, 0
56, 1, 56, 0
57, 1, 57, 0
58, 1, 58, 0
59, 1, 59, 0
60, 1, 60, 0
61, 1, 61, 0
62, 1, 62, 0
63, 1, 63, 0
64, 1, 64, 42

In this case the ID (first column) are just some generated IDs, they could be something else like GUIDs or something unique.

The CharacterID (second column) is the same the whole way because it is referring to the same character. If you store info about characters in the database as well, this ID would point to the table Character which has its own primary key.

The third column represents the inventory slots and they will always be within the 1,64 interval.

The last column refers to actual items. If you store info about items in the database as well, this ID would point to the table Item which has its own primary key. You will three different value in this column: 666, 42 and 0. 666 is an item ID that represents the spear, 42 represents the potion, and 0 is a dummy item ID which represents nothing. I.e. this slot is empty. You could also use null here, if you wish.

How do you get a 2D grid of 8x8 slots out of this 1D array of slot IDs?

Since you know the slots form a grid of 8x8 elements, you can use this information in your favor. Here's a pseudocode example when using 0-based SlotIDs (0-63 instead of 1-64):

int x = SlotID % 8;
int y = floor(SlotID / 8);