mysqlcomposite-primary-keyprimary-key-design

Correct structure and index for a Weekly table

I need a table to store text every week for each user.
So I thought two alternatives:
1) Using composite primary key:

CREATE TABLE `WeeklyTxt` ( 
`Year` YEAR(4) NOT NULL , 
`Week` ENUM('1','2','3','4', ... ,'51','52','53') NOT NULL ,
`UserId` BIGINT NOT NULL ,
`WeekTxt` TEXT NOT NULL,
PRIMARY KEY (`Year`, `Week`, `UserId`)
) ENGINE = InnoDB;

2) Using autoincrement primary key

CREATE TABLE `WeeklyTxt_2` ( 
`WeekTxtId` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Year` YEAR(4) NOT NULL , 
`Week` ENUM('1','2','3','4', ... ,'51','52','53') NOT NULL ,
`UserId` BIGINT NOT NULL ,
`WeekTxt` TEXT NOT NULL
) ENGINE = InnoDB;

I can't figure out what could be the better choice (and why)

Solution

  • It depends of the search in the table that you will usually do!

    Tipically I would use a Simple PRIMARY KEY, and I will add another KEY like your KEY: (Year, Week, UserId)