Multi-action and content notification system

Question

I am trying to understand how best to make a notification scheme for multiple actions and nodes. Basically one should notify someone whenever an action of the type message or enjoy 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 an accurate case of the user name and in another case relate to recover the text and 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 it is line-line, I thought to make a simple select with subquery in each case depending on the type of notification, use concat and explode to break the elements. The above query would return lines 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. I could even resort to views if applicable.

---

_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 - that records the user, the type of event and the reference of the content that suffered the action. Wanted opinions on working this select more simply and/ or efficiently.

Answer 1

I suggest you create a master table of notifications and two daughter tables; one for message notifications and one for likes 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));

When you need to know who likes who, 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 the types of notification by the presence of Nulls - only the likes would have notificao_curtida.curtida_id IS NOT NULL, for example.