CODEWITHSUNDEEP
mysqlprimary-key
Zorann
Zorann

Reputation: 397

MySQL two-column table as primary key

I have an extreamly simple idea: table that keeps user "achievements". And it is as simple as that:

user_id | achievement_id
    1   |    1
    1   |    2
    1   |    5
    2   |    2
    2   |    3

All what I need is user id, and id of achievement if he already got it. All what I need to SELECT is SELECT achievement_id WHERE user_id=x. So no need for an artificial autoincrement column that I'll never use or know what it contains. But setting an primary key is required, so the question is - is it good idea to make such 2-column table and set both columns as multi-column primary key? I already have a set of 3-columns table where 2 are primary key, because it is logic... Well, logic for me, but for the database?

Upvotes: 0

Views: 75

Answers (2)

B. Fernandes
B. Fernandes

Reputation: 39

These types of tables are common in cases of n-n relationships, multivalued attributes, and weak entities. It varies a lot from its modeling, but yes, it is a good solution for some cases. the primary key is usually the relation of the columns. In your case it would be user_id and achievement_id.

Upvotes: 2

AceKing
AceKing

Reputation: 85

Yes since the rule for such a set of n-keys is: "I only want one kind of record which has this set (a,b) of keys". -> therefore you won't be able to add twice "Mario, achievement1".

Primary key will be then (PlayerID, AchievementID).

If you want to add some informations about this achievement (for example, when the player got the achievement), simply do such as: (PlayerID, AchievementID, Date) with PlayerID, AchievementID as primary key.

I hope this will help you.

Upvotes: 1

Related Questions