Design database for history data

Question

I want to create a database with history data score. For example in June 2011 the keyword had score 102. In July 2020 the same word had score 203. What would be the best database design for this. I created this:

CREATE TABLE keywords (
    id bigint,
    keyword character,
);

CREATE TABLE keywords_history (
    id bigint,
    keyword_id bigint,
    date tiemstamp.
);

Can you advise what would be the best design for these requirements?

Answer 1

I guess your keywords won't change, and their scores won't change multiple times on one day, so I come up with a minimalist table:

create table kw_score(
  keyword text not null,
  day date not null,
  score int not null,
  primary key(keyword, day)
);

If you want to read latest value instantly, just create another table with current values.