Primary key on 6 columns

[asmith]

Hello,

 

Using MySQL 5.5 and MyISAM tables.

I have this table which stores points for members on different situations. Other than id_member, there are 5 other columns that specify these situations. 4 of these columns tiny int and one varchar 10. These 6 columns must be unique. So I made a primary key on all 6 and I update the system using such query:

 

INSERT INTO points VALUES (x, y, ....)
ON DUPLICATE KEY UPDATE
points = points + VALUES(points)

 

Everything works fine. I tried combining the 5 columns into 1 varchar field separated with coma. So that my primary key goes on 2 columns, id_member and id_situation. It is working with the same speed or maybe a little bit faster but combination of 5 values into one columns make things for other queries harder.

I want to know is it a good practice to have primary key on 6 columns? Is there a big downside on such design? This table gets updated A LOT and it is easier for me to have the columns separated than combine them into one.

 

Thanks for your time.

[mikosiko]

I don't follow.... why are you doing a PK with the 6 columns?.... id_member  seems to be the candidate field to be the PK (an AUTO_INCREMENT field), and if the remaining 5 fields need to be UNIQUE all together... well... use an UNIQUE constraint covering those 5.

[asmith]

it is not members table to have pk and auto_increment. It is points table based on members activity and each member can have different points for different activity. The whole 6 columns must be UNIQUE for example:

 

id_member member_type id_forum id_group id_sport id_season points

23              1                    2            3            4            2              45

23              1                    3            2            4            2              60

 

the first 6 columns must be UNIQUE.

[fenway]

I still don't see what UNIQUE and auto-increment have to do with one another.