1. Database,其实 @mysqlops 回答就是微薄最基本的数据库方式,我在上面做一下扩展。
微薄内容表A:tid uid src_tid content timeline,其中 tid 是微薄的 ID (自增量),src_tid[1]为转发的源 tid 。
话题表B:kid title lastupdatime total,total是话题总数,kid 是话题的ID (自增量)
话题关联表C:id tid kid,id无意义
@用户关联表D:id uid tid,这里的uid是指被提及人的uid,id无意义
收听用户关联表E:id uid follow_uid
上面的 timeline、lastupdatime 均为“发帖时间”,其中timeline是永久不变的字段, lastupdatime 为“该话题最后发帖时间”,属于冗余字段,等同于 SELECT TOP 1 timeline FROM A INNER JOIN C ON C.tid = A.tid WHERE C.kid = #话题id# ORDER BY A.timeline DESC。[1] src_tid 为何可以这样设计的原因请阅读 "4.发微薄" SQL:
follow 用户列表:SELECT follow_uid FROM E WHERE uid = 102
微薄首页微薄列表:SELECT content,(SELECT content FROM A AS a2 WHERE a2.tid = a1.src_tid AND a1.src_tid > 0) AS src_content FROM A AS a1 WHERE uid IN (SELECT follow_uid FROM E WHERE uid = 102) ORDER BY timeline DESC
某 #话题# 列表:SELECT A.content,(SELECT content FROM A AS a2 WHERE a2.tid = a1.src_tid AND a1.src_tid > 0) AS src_content FROM A AS a1 INNER JOIN C ON C.tid=A.tid WHERE C.kid=#话题id# ORDE BY A.timeline DESC
@我 的列表:SELECT A.content,(SELECT content FROM A AS a2 WHERE a2.tid = a1.src_tid AND a1.src_tid > 0) AS src_content FROM A AS a1 INNER JOIN D ON D.tid=A.tid WHERE D.uid=102 ORDE BY A.timeline DESC
转播列表:SELECT content,uid FROM A WHERE src_tid = 源tid ORDE BY A.timeline DESC