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?


1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.