>>>>一个帖子可以具有非常多的回复is 回复 flat or hierarchical?>>>>由于估计有200人同时在线,1-2w注册用户,所以效率也很成问题~what kind of database? what kind of server environment?here is a typical table design for sql server1. users table
userid int identity
username nvarchar(20)
password nvarchar(20)
....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 = 1232)一个帖子可以具有非常多的回复select * from posts where postid = 123 or parentpid = 1233)可以方便的查找都某个人的所有主贴和所有参与的帖子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 desc5)可以按标题或内容查找贴子,并且可以指定在哪几个版块内查找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
userid int identity
username nvarchar(20)
password nvarchar(20)
....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 = 1232)一个帖子可以具有非常多的回复select * from posts where postid = 123 or parentpid = 1233)可以方便的查找都某个人的所有主贴和所有参与的帖子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 desc5)可以按标题或内容查找贴子,并且可以指定在哪几个版块内查找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
解决方案 »
- 获取flash播放器当前的播放时间
- .net 伪静态后,如果页面上有Button按钮就会出错
- 关于ViewState的一个问题
- 传递参数有问题!在C#那边解决不了,来asp看看
- 数据集xsd问题:数据源绑定没问题,自己写代码就出错
- 做这样一个系统大概多少钱啊?
- Session传值的问题
- ScrewTurn Wiki
- 数据库查询搜索语句应该怎么写才能搜索到内容?我这样写什么也没有搜索到~
- 有谁知道asp 或asp.net 有比较成功的中间件没有,就象java 的中间件一样?
- 请问能否在控件(编译成.dll文件中的类)里引用Application?
- 请问:现在看"ASP.NET入门经典1.0--C#篇"是不是不太合适?
我最早写论坛的时候就是用的这种方法阿,这种方法有缺点,posts table太大了!到了后期,posts table会超过100w条记录,这个时候,列一个帖子需要在全表搜索他的回复。而且如果各个版块的发贴时间不是很平均的话,或导致某些版块列前30贴的时间也很久。不过这种方法唯一的好处就是搜索的时候不需要跨表搜索。后来考虑把posts table分开,回复和主贴采用不同的表,在主贴表中添加一个字段,列举所有属于该表的回复。这种方法也不高明。不知道有没有更加优美的方法~
不知道像csdn,mop这种的大论坛是如何设计的啊。 还有楼上的,凡事不要想的太简单