-3

I want to store game Inventory information into SQL.

What method should I use to best benefit performance costs?

The data I want to store is:

  • Slot ID (from 0 to 608);
  • Item ID (from 0 to 66000);

I can think about 2 ideas:

  • Using " ; " separators, and all 608 Items goes into 1 column...
UserID   | Items
---------+-----------
70001    | 3001 ;10000;65001;...
70002    | 0    ;0    ;0    ;...
70003    | 1    ;0    ;10   ;...
  • Or new Table for each user (UserID + Inv) where I can store like this:
table: 70001Inv

SlotID    | ItemID
----------+-----------
000       | 3001
001       | 10000
002       | 65001
Angelo Silva
  • 5
  • 2
  • 1
    Define: "struggling". – Dai Oct 25 '19 at 01:21
  • 2
    ".. about " ; " separators, and everything goes into 1 column" - don't do this, it defeats the whole point of using a relational database. If you just want to store blobs then use a key/value store like MongoDB or BerkeleyDB. – Dai Oct 25 '19 at 01:22
  • 1
    Doesnt matter where the data came from - they go to a sql database. At the moment i think you are that confused that you go to bed sleep a bit and tomorrow you go to google and read a few tutorials. Then you might be able to sort and address your question. At the moment it sounds like i want it all, ive no meaning about anything but i want it for free :) – Thomas Ludewig Oct 25 '19 at 02:32
  • Sorry, i guess I really needed sleep. "struggling" -.- jesus, i am ashamed... I didn't want to use much of performance when consulting that user inventory (600 items within one consult). @Dai , fast research about blob, and I guess that's the one thing I needed. I am very basic programmer, altho I usually find solution by myself, but about performance costs I know zero about. – Angelo Silva Oct 25 '19 at 15:46

1 Answers1

3

The third method is probably the best method . . . a single table with three columns:

UserID  | SlotID  | ItemID
--------+---------+-------
XXX     | 000     | 3001
XXX     | 001     | 0
XXX     | 002     | 65001

That is, the UserID is stored in the table, with a separate row for each combination.

Angelo Silva
  • 5
  • 2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wouldn't this be really high performance consuming when consulting all rows for that user id? My biggest concerning is about performance. Number of rows VS quantity of data into a single column/row – Angelo Silva Oct 25 '19 at 15:41
  • It's always going to be a tradeoff. What if you want a query that finds every slot that contains item 3001? Storing item lists as `;` separated strings makes that type of search have very bad performance. – Bill Karwin Oct 25 '19 at 16:01
  • @BillKarwin I guess I am being over careful, and unware of sql performance costs... I can't still manage by myself what's the best method, but this 3rd seems to be perfect for later consults. – Angelo Silva Oct 25 '19 at 16:03
  • Every type of optimization optimizes for _one_ type of query, at the expense of other types of queries. If you store data according to rules of normalization, you will have a compromise that probably gives "good-enough" performance for all types of queries. But the real purpose of normalization is to prevent update anomalies. – Bill Karwin Oct 25 '19 at 16:18
  • @AngeloSilva You might like to read my answer to [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 25 '19 at 16:19
  • @BillKarwin first time reading about **1NF** thanks, it helps me sort the things oout more logically acceptable by sql basics (btw to all previously, english is not my mother tongue, but I feel like I get more enlightment from you guys) – Angelo Silva Oct 25 '19 at 17:48
  • @AngeloSilva . . . If you want all rows for a given user, you can create an index on `user_id`. – Gordon Linoff Oct 25 '19 at 19:45