Notification system for multiple action and content

Question

Notification system for multiple action and content

Navigation

#1 by (2 votes)

1

I'm trying to understand how best to make a notification scheme for multiple actions and nodes. Basically you should notify someone whenever a message or tan action is performed.

Message: Fulano de tal enviou uma mensagem

Enjoyed: Fulano de tal curtiu seu texto [prévia com texto]

The problem is to relate all this, because a precise case of the user name and otherwise relate to retrieve the text and the link.

select
case tipo
    when 'mensagem' then ( [...] )
    when 'curtiu'   then ( select concat( nome , '//' , acao , '//' , body ) from tabela
                           inner join [...] where [...] )
end 'data'
from notificacao

As line-line, I thought of doing a simple select with subquery in each case depending on the type of the notification, use concat and explode to break the elements. The above query would return rows as Papa Cahrlie//curtiu//Lorem ipsum... .

In this scheme each type would have a subquery, but I have more 'events' besides the example, and I will have more complex querys. You could even use views if that's the case.

TABLE:

CREATE TABLE 'notificacao' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'USUARIO' int(11) DEFAULT NULL,
  'CONTEUDO' int(11) DEFAULT NULL,
  'TIPO' enum( '...' ),
  'STATUS' enum( '0' , '1' ) DEFAULT '0',
)

My table is simple - generic -, which records the user, the type of event, and the reference of the content that underwent the action. I wanted opinions on working with this select more simply and / or efficiently.

php mysql modelagem

asked by anonymous 03.06.2015 / 01:35

1 answer

how to write a name using pointers How to solve value quotes in html with javascript?

score 2 · Answer 1

I suggest you create a notifications master table and two child tables; one for message notifications and one for tanned notifications:

CREATE TABLE notificacao (
    notificacao_id INT NOT NULL AUTO_INCREMENT,
    lida BOOLEAN NOT NULL DEFAULT FALSE,
    data_criacao DATETIME NOT NULL,
    -- outros campos, e.g. data/hora que a notificação foi lida…
    -- você pode também desnormalizar o banco de dados e colocar aqui o id do usuário
    PRIMARY KEY (notificao_id));

CREATE TABLE notificacao_mensagem (
    notificacao_id INT NOT NULL,
    mensagem_id INT NOT NULL,
    PRIMARY KEY (notificacao_id),
    FOREIGN KEY (notificacao_id) REFERENCES notificacao,
    FOREIGN KEY (mensagem_id) REFERENCES mensagem (id));

CREATE TABLE notificacao_curtida (
    notificacao_id INT NOT NULL,
    curtida_id INT NOT NULL,
    PRIMARY KEY (curtida_id),
    FOREIGN KEY (notificacao_id) REFERENCES notificacao,
    FOREIGN KEY (curtida_id) REFERENCES curtida (id));

At the time you need to know who liked whom, you make the appropriate join. If you need to pull everything into a table only later, you can compose a view using LEFT OUTER JOINs:

CREATE VIEW notificacao AS
SELECT
    …
FROM
    notificacao
    LEFT OUTER JOIN (
        notificacao_mensagem
        INNER JOIN mensagem ON …condição…) USING (notificacao_id)
    LEFT OUTER JOIN (
        notificacao_curtida
        INNER JOIN curtida ON …condição…) USING (notificacao_id)
    …outras notificações…

You would differentiate notification types by the presence of NULLs - only tanned ones would have notificao_curtida.curtida_id IS NOT NULL , for example.