0

Since I can't store lists, should I add a column to my table called owned games and then just keep a long string like "game1, game2..."?

Or should I add another table called something like ownership and every row represents the fact that a user is linked to a game...

  ownership

  - gameID
  - owner
  - dateAdded
  • 3
    Look for a "many to many" relationship in relational databases, you will find lots of examples. Basically you need 3 tables: User, Game and GameByUser (or Ownership). This last one links the previous 2 together so a user can have many games and a game can be owned by many users. – EzLo Mar 25 '19 at 16:11
1

You first need to decide: can a game be owned by more than one person? (consider also if the answer might possibly be yes in the future).

If yes) Then you have a many-to-many relationship and as you described the only way to do this is to use an intermediary table with atleast these three columns:

  • ID
  • Game_ID (foreign key referencing the primary key in the Game table)
  • Owner_ID (foreign key referencing the primary key in the Owners table)

The naming convention for an intermediary table is normally a combination of the two tables you are connecting. So in your case it would probably be 'Game_Owner'.

If no) You have a one-to-many relationship and no intermediary table is required. In the Game table include a column called 'Owner_ID' which is a foreign key.

|improve this answer|||||
0

I think the answer will hinge on what you mean by a "collection of games". So you must first ask yourself the following:

Do the records in my game table represent games in the real world or just the idea of a game?

This will all depend on what you will use your database for. For example, suppose you want to build a game night advertising service and every host must register what games they own. In that case, you only need the idea of a game since multiple people could "own" Donkey Kong. However, if you want to do a lending library then you'll need to treat each record as a physical game in the real world. Otherwise how would you know to whom you should return the game when you are done borrowing it?

Anyway, if its a physical game in the real world, then there can really only be one owner at a time. You just need a one-to-many relationship from game to owner, so you can use a foreign key reference from game to owner.

game.userID

Then if you want to see all the games someone owns you do:

SELECT * FROM game 
WHERE userID = <id of someone>

However, if you mean the idea of a game, then the same game record could be owned by many different people. You should do a many-to-many relationship with a 3rd table to model the concept of "ownership" - exactly like you said.

Then if you want to see all the games someone owns you do:

SELECT game.* FROM game 
JOIN gameOwnership ON game.id = gameOwnership.gameID 
WHERE gameOwnership.userID = <id of Art3mis>

You can also reverse it and see all the people who own a certain game:

SELECT user.* FROM user 
JOIN gameOwnership ON user.id = gameOwnership.userID 
WHERE gameOwnership.gameID = <id of Donkey Kong>
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.