Sign in to follow this  
Bru

effiecient way of storing items.

Recommended Posts

Bru    100
hey there. right now i am working on making an inventory for each character in my online game,but i encountered a problem while considering how to make it work database wise. as for now, i only have a table where every line is a user, i thought of adding every line 21 cells(there are going to be 21 item spots in my game),but this could get heavy since even empty cells take space. i also thought about making another table specialy for items with each row containing the item id and the person that has that special item. this would be more light, but when i think about it, the scanning for the items could get slow since this table is going to be larger than the users table. i am not realy sure what would be an effective way to handle items database wise. does anyone have an idea of what should i do?

Share this post


Link to post
Share on other sites
frob    44903
What you just described is quite small. The PC can handle arrays of many thousands of items without performance issues.

If you are concerned about unused items, consider using an associative container. You didn't mention your source language. In c++ that would be std::map, in C# it would be System.Collections.Generic.Dictionary. In other languages you'll need to look it up.

Share this post


Link to post
Share on other sites
Adam_42    3629
If I interpreted the original post right I think this is for an SQL database, and not for in memory storage.

I'd say go with the one big table containing one UserID, one ItemID, and some indication of where in your inventory it is per row. Index it on UserID of course so lookups aren't too slow. That seems to be the simplest design.

However I'd also strongly suggest hiding the implementation details behind a small number of functions (or stored procedures), so that if you later find that it's inefficient then you can change it without a major code rewrite.

Share this post


Link to post
Share on other sites
Bru    100
i'd like to ask another question if it's ok.
i just noticed i realy dont know any way to count the number of rows my select query gets.
sure i can use FetchNext() in a while loop, but i need to know the number of rows in advance.
i am using SQLAPI++ with c++, the offical website didnt seem to show me any solution for that.

is there a way to know the size in advance?

Share this post


Link to post
Share on other sites
ROBERTREAD1    100
Although it's not very efficient you could run 2 queries inside a Transaction (to block other updates from messing with the numbers)

First

SELECT COUNT(*) AS NumberToReturn
FROM....

Then

SELECT blah, blah1, blah2
FROM...

Share this post


Link to post
Share on other sites
Bru    100
woah i dont think i realy understand what's going here. why use two queries? and is NumberToReturn a variable defined before the query?
not efficient you say? maybe i should stop being lazy, just make an array and count inside a loop while using FetchNext (and inserting results into the array?).

what i am trying to do is retrieve the id if of all items in database that belong to some player,i then need to send it to the player with packet format of :packetsize->packetid>number of rows retrieved->id of item->id of item...

Share this post


Link to post
Share on other sites
ROBERTREAD1    100
Well just build the packet on the fly recording the position of the Item_Count and then fill it in at the end once you know how many (i.e. increment count until FetchNext "fails").

Build your select like this (or use a Stored Procedure)

SELECT Item_ID
FROM UserItem
WHERE User_ID = {UserID}

Replacing {UserID} with the ID of the User who needs the data.

Share this post


Link to post
Share on other sites
It greatly depends on what your items are. What ROBERTREAD1 describes is the "standard way" (fifth normal form, if I'm not mistaken) of implementing it, if every item is individual. It also lets you give any number of items to players without having a fixed overhead or making anything noticeably slower. Joins on indexed columns are extremely efficient, the difference between 20 and 2000 items in a set is hardly noticeable.

If, on the other hand, you only have a few hundred/thousand distinct items which are not individual, and you know that you only ever want to allow 21 items anyway, it is much more efficient to simply store 21 item indindices (a 16 or 32 bit integer) and to keep the item data in RAM.
Woe if you find out that 21 items aren't enough at a later time, though. Then you'll have to run an update which may take minutes to hours if your database is of non-trivial size.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this