I'm trying to create a database schema for Game achievements which should include these:

Users: username, avatar image file location, achievements their received in each game

Each achievement has achievement name achievement level
achievement type a game that achievement was part of. Each achievement is specific per game. You can’t have the same achievement across different games

Each game has a title and game type.

It also must have clear relationship definitions (crow’s feet for 1 to many), primary key and foreign keys should be marked.

I've started it but I'm not sure if I did it right and I'm not really sure which of the information types are supposed to be primary keys and which supposed to be foreign keys

The image shows what I have so far and where I'm stuck (stuck on the keys)

CodePudding user response：

Primary keys must be unique value, You should give appropriate primary keys for each, as:

Users: user_id,

games: game_id,

achievements: achievement_id,

Then you can specify the foreign keys accordingly, like in achievements, user_id, and game_id will be foreign keys.

CodePudding user response：

Any key that you want to make foreign key then their should be a table where that key present as a primary key. in your case Users table

1. username primary key
2. avtar_image
3. achievment_name (foreign key) reference to Achievements table (achievment_name) if you want game also in user table then add a new column in User table
4. game_title (foreign key) reference to Game table(game_title)