I'm working on a new web platform that has users, groups and those users can get achievements (each group has its own achievements) for doing specific tasks and these are the tables I have at the moment:
My question is, in the User_achievements table, should I store the user and group id's or only the group_user id that holds the relation between group and user?
First off to answer your question, I would store just the user_id as a foreign key in the user_achievements table as the achievements are linked to the user not the group in this table.
If the achievements are also linked to the group, as you mentioned in a comment to another answer, that relationship should be shown in the achievements table, not the user_achievements table.
Also why would the Group_users table have its own unique id (which you seem to be implying that it is), is it not just a way to join a many to many relationship? If this is the case why not just make the primary key of the group_users table a combination of the user_id foreign key and the groups_id foreign key. There is no need for a unique id for the table, as the combination of the two foreign keys is unique in itself.
EDIT:
So I'd do it like this:
Users table:
id (Primary Key)
... What ever other data you want to store for the user ...
Groups:
id (Primary Key)
... What ever other data you want to store for the group ...
Group_users:
user_id (Foreign Key to users.id) (Part of primary key)
group_id (Foreign Key to groups.id) (Part of primary key)
Achievements:
id (Primary Key)
group_id (Foreign Key to groups.id)
... What ever other data you want to store for the achievement ...
User_achievements:
achievement_id (Foreign key to achievements.id) (Part of primary key)
user_id (Foreign key to users.id) (Part of Primary Key)