I'm a little confused since your second paragraph sounds like you're talking about the list of items the user actually has, while the third paragraph sounds like it's talking about the list of all item types that exist in the game. The latter absolutely has to be in the client for obvious reasons, and there's no point putting it in a database since it's the same for all clients, so hopefully that's not what you meant.
but having multiple players updating a database all the time is slow? (Correct me if I am wrong!)
Nope. You may have to configure your database to get the performance you want, but generally speaking any modern database will have a lot of ways to tweak performance based on things like your expected ratio of reads to writes, write frequency, read frequency, etc. Most importantly, it sounds like no two users ever have to update the same record (they're only storing
their information) so this should be no problem at all.
Though I have no idea how big your userbase will be and how many times you plan on updating each one's data. You'll probably want to speed test a few databases once you have some good estimates on how many writes you actually need.
The user has their ... password and other details stored in a database ...
Hopefully those things aren't stored in plaintext.
And finally:
my first initial idea to store the users value was to have a secondary table with a foreign key from the item table and the player table
How to design the database is going to be the hard part. Or more accurately, the easiest part to screw up without knowing it. I've had to do a lot of this at my job, so here's my advice: Plan this out in advance like crazy. Nothing is more irritating or fiddly than having to break everyone's data and rewrite a bunch of code because of a subtle mistake back in the design phase. All the code "on top of" the database is a lot easier to fix later.
For instance, write down the exact SQL queries you want to make on this database. Rank them by importance. Make sure whatever database schema you use makes it not only easy to write the queries, but easy to add indexes on the frequent/important ones should you need to make them faster. Then write down some changes you might conceivably want to add to the game in the future (adding new item types, supporting custom item filters/sorting, adding some kind of shared inventory features, who knows) and think through how you would change the database in a backwards-compatible way to support them. Once you're sure how much of that your design can handle, and you're satisfied with that, then you're good. The final design is usually quite simple, but picking the right simple design pays off big time.
