1

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)

genespos
  • 3,211
  • 6
  • 38
  • 70

1 Answers1

0

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)

Daniel Madurell
  • 78
  • 8
  • Do you think a `KEY` is enough? Don't you think is better using a `UNIQUE`? Why choose one instead of the other? – genespos Apr 29 '16 at 10:25
  • Yes of course! UNIQUE is better! You need to think the queries that you will use in this table, for example you will need to update or to Join with another table with `WeekTxtId` then your second option is OK. You will show into a dashboard the information of this year, last week of the current user? then your first option is better. – Daniel Madurell Apr 29 '16 at 12:18