How do i create an entry if it not exists and if it exists i just add the date?

Question

I have a table TRACKS with:

id integer NOT NULL,
title text,
artist_name text,
album_name text,
date text,
PRIMARY KEY (id),
FOREIGN KEY (artist_name) REFERENCES artists(name),
FOREIGN KEY (album_name) REFERENCES albums(name)

What i want to do is, if the entry for the song exists (same artist, same album name, same song title) to add the entry date (which is YYYY-MM-DD). I don´t care about the time, just the date.

So i have one entry for the song, but various dates when it was played. I want duplicate dates, because that way i can count number of plays.

Here are the other tables:

ALBUMS:

id integer NOT NULL,
title text,
artist_name text,
PRIMARY KEY (id),
FOREIGN KEY (artist_name) REFERENCES artists(name)

ARTISTS:

id integer NOT NULL,
name text UNIQUE,
PRIMARY KEY (id),
FOREIGN KEY (artist_name) REFERENCES artists(name)

I can use

INSERT OR IGNORE INTO artists(name) VALUES(?)

For artists because name is UNIQUE, but the others i don't know how to do it because different artists can have the same album name and song names.

SAMPLE DATA:

I will feed the database with "Artist", "Song", "Album" and the system date in YYYY-MM-DD format.

program.py -a "Michael Jackson" -t "Billie Jean" -d "Thriller" and the program will automatically feed the YYYY-MM-DD.

So today i give the app -a "Michael Jackson" -t "Billie Jean" -d "Thriller" three times.

Tomorrow i listen to it two more times.

I want the program to create the artist, album, song and date in the first time and add two more 2021-06-24 in TRACKS without duplicating everything else.

And tomorrow it will add 2021-06-25 two times.

It has to be in a way that i can COUNT() the number of dates to know how many times i played the song, the album or the artist.

Answer 1

A normalized schema for your requirement would be to have a separate table where you insert a row (with the track's id and the current timestamp) each time you listen to a track.
So, no date column in the table TRACKS.

Also, you should store the album's id in the table TRACKS and not its title.
This is common practice and there is a reason for it: there are albums with the same title.
Also, there is no need to store the artist, because you can get it from the album's id.

CREATE TABLE ALBUMS(
  id integer NOT NULL,
  title text,
  artist_id integer,
  PRIMARY KEY (id),
  FOREIGN KEY (artist_id) REFERENCES artists(id)
);

CREATE TABLE ARTISTS(
  id integer NOT NULL,
  name text UNIQUE,
  PRIMARY KEY (id)
); 

CREATE TABLE TRACKS( 
  id integer NOT NULL,
  title text,
  album_id text,
  PRIMARY KEY (id),
  FOREIGN KEY (album_id) REFERENCES albums(id)
);

CREATE TABLE LISTEN(
  track_id integer NOT NULL,
  date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (track_id, date),
  FOREIGN KEY (track_id) REFERENCES tracks(id)
);

You can insert a new row to the table LISTEN just by providing the track's id:

INSERT INTO LISTEN(track_id) VALUES (152);

The date will be by default the current timestamp.