2. boards table
boardid int identity
boardname nvarchar(30)
3. posts table
postid int identity
parentpid int (0 for initial topic, postid for followups or replies)
boardid int (boardid from boards table)
userid int (userid from users table)
topic nvarchar(100)
content nvarchar(8000) (if more space needed, use ntext/image type)
posttime datetime
1)帖子具有唯一的id,只需要这个id不需要知道帖子属于哪个板块就可以访问
select * from posts where postid = 123
2)一个帖子可以具有非常多的回复
select * from posts where postid = 123 or parentpid = 123
3)可以方便的查找都某个人的所有主贴和所有参与的帖子
select * from posts where userid = 1 and parentpid = 0 (所有主贴)
select * from posts where userid = 1 and parentpid <> 0 (所有参与帖子)
4)板块很多,但是选择某个板块依旧可以快速的显示该板块最新的帖子
select top 30 * from posts where boardid = 1 order by posttime desc
5)可以按标题或内容查找贴子,并且可以指定在哪几个版块内查找
select * from posts where topic like '%hello%'
select * from posts where content like '%hello%'
select * from posts where topic like '%hello%' where boardid = 2
select * from posts where topic like '%hello%' where boardid in (1,2,3)
consider to use full-text search if for performance issues, you don't want to use LIKE