The first thing to do – DB design

数据库准备

我们从今天起开始做一个更接近实际的例子，以便更深入的探讨MVC 的各种特性。

一般工作中的项目负责人会启动一个新的工作任务，并明确该项目的具体工作成员。为了最终完成工作任务，他需要了解成员的每日工作情况（工作报告），工作进展情况， 并可能根据报告给出处理意见和建议。

生活中，不能能保证每个成员都在线，那么一条短信或电子邮件就是很好的辅助。

最后，工作任务都会结束，即便最后的结果是未完成或未完全完成。那么，负责人就应该对此工作给出最后的结语。

下面是根据以上情况简单设计的数据库表，我们今后的应用开发都会基于此数据库结构。细心的读者会发现没有记录用户信息的表，是因为我们将采用asp.net  membership来处理应用的安全及用户信息，非常方便易用。

 

The first thing to do – DB design

The application I am going to develop is a task management application, which will carry general requirements in team manage.

 Let’s say project leader launch a mission (Task) that is expecting to be completed in certain amount of time. He assigns this mission to several team members. He would be interested at the process of the mission, how it is going, what phase it is at, who is dealing with what etc.  In short, he may want to know exactly every member’s daily duty. After the leader receives report from team members, if it is necessary, he may make comment to the member. For instance, give the member a working tip or suspend the member to cope with something urgent. 

In real world, you cannot expect people online all the time, so a short message may be handy beside email notification.

At the end, mission has to be closed whether it is completed or not, but leader could leave a comment to summarize the whole job.  Ok, that’s pretty much scenario we need to know about goal of this application. Let’s begin with database design:

The following is the database tables we will need for the application. It’s pretty simple, so no more gossipy word. Please see the tables in the following.

 

任務表 (TASK)

任務序號	Int	TaskID
任務名稱	nvarchar(50)	TaskName
任務描述	nvarchar(300)	TaskDescription
任務狀態	Bit	Flag （History）
建立日期	Date	CreateDate
啟動日期	Date	StartDate
計畫完成日期	Date	EndDate
關閉日期	Date	CloseDate
行號	Unique	RowGuid
关闭注释	Nvarchar(1000)	CloseComment

                                                                                                                       

 

任務任命用戶表 (TASKUSER)

任務用戶序號	Int	TaskUserID
任務序號	Int	TaskID
用戶序號	Unique	UserID
發起人標記	Bit	UserTaskRoleFlag
行號	Unique	RowGuid

 

任務詳情表 (FOOTAGE)

任務詳情序號	Int	FootID
添加日期	DateTime	CreateDate
描述	nvarchar(1000)	Description
行號	Unique	RowGuid
发起人注释	Nvarchar(1000)	Comment
注释日期	Datetime	CommentDate
用戶序號	Unique	UserID

 

附件表 (ATTACHMENT)

附件序號	Int	AttachmentID
任務詳情序號	Int	FootID
附件	Vbinary(Max)	Attachment
行號	Unique	RowGuid
附件名称	Nvarchar(50)	FileName
附件类型	Nvarchar(50)	FileType

 

 

短信表 (SMS)

短信序號	Int	MessageID
發出用戶序號	Unique	UserID
接受用戶序號	Unique	UserID
短信	Varchar(100)	Message
已讀標記	Bit	Flag
行號	Unique	RowGuid

You may wondering why there is not User table that contains people information.  You are right, there is not user table in this design, because I am planning to implement Asp.Net membership as security control and user store. It’s easy and efficient.