How to create a followers table?

Question

I’m doing a tcc project, which is kind of a social network..., I want to create a followers system but I don’t know how to put this in the database or how to relate the data I already have

this and the user table

 CREATE TABLE usuario(
    id_usuario      SMALLINT     AUTO_INCREMENT,
    nome            VARCHAR (50) NOT NULL,
    sobrenome       VARCHAR (50) NOT NULL,
    apelido         VARCHAR (25) NOT NULL,
    email           VARCHAR (60) NOT NULL,
    senha           VARCHAR (15) NOT NULL,
    numero          VARCHAR (11),
    id_status       VARCHAR(4), 
    data_nascimento DATE         NOT NULL,
    cpf             VARCHAR (11),
    
    CONSTRAINT pk_id_usuario PRIMARY KEY (id_usuario),
    CONSTRAINT fk_id_status  FOREIGN KEY (id_status) REFERENCES status (id_status)
);

and this is the artist’s table

 CREATE TABLE artista(
    id_artista  SMALLINT     AUTO_INCREMENT,
    descricao   VARCHAR(200) NOT NULL,
    id_usuario  SMALLINT     NOT NULL,
    -- seguidores  INT, -- 
    rede_social VARCHAR(200),
    
    
    CONSTRAINT pk_id_artista PRIMARY KEY (id_artista),
    CONSTRAINT fk_id_usuario_artista FOREIGN KEY (id_usuario) REFERENCES usuario (id_usuario)
);

I don’t know how to make such a relationship since the artist is also a user.

Answer 1

I believe that the best alternative would be to create a third table containing a user/artist N/M ratio

CREATE TABLE seguidores (
    id_artista  SMALLINT,
    id_usuario  SMALLINT,
    CONSTRAINT fk_id_seguidores_usuario FOREIGN KEY (id_usuario) REFERENCES usuario (id_usuario),
    CONSTRAINT fk_id_seguidores_artista FOREIGN KEY (id_artista) REFERENCES artista (id_artista)
 );

If you want to count how many followers an artist has enough

SELECT COUNT(id_artista) WHERE id_artista = IdDoArtistaRequerido FROM seguidores;