Good people,
I need to make a follow-up system for my site users like notifications but I never did and I do not know where to start, I needed an idea how I can do this system.
Good people,
I need to make a follow-up system for my site users like notifications but I never did and I do not know where to start, I needed an idea how I can do this system.
You will need a basic Many-to-Many
_report in your bank (a user can be followed by 0-n
users, and can follow 0-n
users). Basically, you'll need a table that stores follower
and followed
, here's an example of those tables:
mysql> desc usuario;
+---------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-------------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| nome | varchar(75) | NO | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-------------+------+-----+-------------------+----------------+
mysql> desc conexao;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| follower | int(10) | NO | MUL | 0 | |
| followed | int(10) | NO | MUL | 0 | |
+----------+---------+------+-----+---------+----------------+
Explaining: In the usuario
table you will have any of your users' data as any default user table, in your conexao
relationship table, you will store id
user to be followed and user follower ( followed
and follower
respectively).
Example query to see who follows the user x:
SELECT usuario.nome
FROM conexao
INNER JOIN usuario ON conexao.follower = usuario.id
WHERE conexao.followed = @usuario_x_id;
Code CREATE
and INSERT
of this response:
CREATE TABLE IF NOT EXISTS 'usuario' (
'id' int(10) NOT NULL AUTO_INCREMENT,
'nome' varchar(75) NOT NULL,
'created' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO 'usuario' ('id', 'nome', 'created') VALUES
(1, 'Eduardo', '2015-02-03 16:11:22'),
(2, 'Maria', '2015-02-03 16:11:29'),
(3, 'João', '2015-02-03 16:11:36');
CREATE TABLE IF NOT EXISTS 'conexao' (
'id' int(10) NOT NULL AUTO_INCREMENT,
'follower' int(10) NOT NULL DEFAULT '0',
'followed' int(10) NOT NULL DEFAULT '0',
PRIMARY KEY ('id'),
KEY 'FK__usuario' ('follower'),
KEY 'FK__usuario_2' ('followed'),
CONSTRAINT 'FK__usuario' FOREIGN KEY ('follower') REFERENCES 'usuario' ('id'),
CONSTRAINT 'FK__usuario_2' FOREIGN KEY ('followed') REFERENCES 'usuario' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
INSERT INTO 'conexao' ('id', 'follower', 'followed') VALUES
(1, 1, 2),
(2, 1, 3),
(3, 2, 3),
(4, 3, 1);