Topic: 想问问这里的大侠们，有一道sql database design难题，公司有一个网上survery. @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / 想问问这里的大侠们，有一道sql database design难题，公司有一个网上survery. -howcanido_helpme(howcanido); 2007-5-23 {1057} (#3694916@0)

我本来觉得varchar是变量，但是设置varchar时要问你size varchar(500) 我想可能会有区别的，要不然直接就varchar也不用问你具体size了。如有不对请多多指教。谢谢 -howcanido_helpme(howcanido); 2007-5-24 (#3697169@0)

1. I didn't see the varchar column is part of an index. Separating it to two tables with varchar(500) and varchar(5000) won't help anything(performance, storage) but making it complicated. -looi500(looi); 2007-5-25 (#3699698@0)

我的sql 基础不够好，总以为sql server 对 varchar(500) 和 varchar(5000) 会有不同的设置，比如预留空间什么的。所以当时我认为varchar(5000)会比较浪费space -howcanido_helpme(howcanido); 2007-5-29 (#3707097@0)

如果没有区别，那么所有的列（姓名，性别，部门，……）通通设为varchar(5000)就好了，还要数据库设计干什么？ -newkid(newkid); 2007-5-29 (#3707982@0)

Please explain what the benefit of separating it to two tables is for this case.I didn't say you should define varchar(5000) for any varchar column. If you define 性别 as varchar(5000), you can image what problems you may have for the application. For varchar column, just keep the actual max length is fine. -looi500(looi); 2007-5-29 {229} (#3708337@0)

如果“actual max length ”是500,那就没有必要用5000. 这可不是480和500的区别。好处是：存储紧凑，读写速度快。具体到这个应用，那要请楼主举例。 -newkid(newkid); 2007-5-29 (#3708764@0)

存储紧凑? VARCHAR500 和VARCHAR5000一样紧存储紧凑. 读写速度快? 两个TABLE怎么能快. -looi500(looi); 2007-5-29 (#3709030@0)

2. Are there any problems for you to store the int answer to the varchar column? -looi500(looi); 2007-5-25 (#3699703@0)

现在想来int是没有太大问题，可能是text和binary。这个我可能必须存在另外的table。因为有时survey里有textbox。有时会有uploadfile option -howcanido_helpme(howcanido); 2007-5-29 (#3707127@0)

我的经验是把不需要检索的东西（二进制文件）放到文件服务器上就好，数据库里最多存个路径、文件名 -newkid(newkid); 2007-5-29 (#3707986@0)

数据库不只是有检索的功能. 把数据存在FILE SYSTEM上, 会LOSE很多的数据库的功能. 比方说TRANSACTION ROLLBACK的功能. 还有数据BACKUP的问题. -looi500(looi); 2007-5-29 (#3709048@0)

文件要进入数据库之前，必须从客户端上传到FILE SYSTEM; 这时候即使你ROLLBACK, 文件也已经存在。FILE SYSTEM照样可以备份。假如你不在乎开销，把二进制文件存到数据库里当然也没错。 -newkid(newkid); 2007-5-30 (#3709508@0)

The rollback should backout any changes that were maked within the transaction. The data file can't be backed out from the file system together with database, it is the problem. -looi500(looi); 2007-5-30 (#3710977@0)

我就是这个意思啊？正因为文件入库前已经上传，ROLLBACK的好处就享受不到了，你照样得手工再删除文件。 -newkid(newkid); 2007-5-30 (#3711077@0)

我觉得你的设计没有什么毛病，速度也不会受影响。做个视图把三种answer表 UNION 起来（当然INT要转换为字符串），访问这个视图就像一张表一样。 -newkid(newkid); 2007-5-24 (#3696943@0)

非常感谢，想问一下视图是不是view? 我就是没想到用view -howcanido_helpme(howcanido); 2007-5-24 (#3697174@0)

视图就是view. 你的table question还要多一个列表明answer是属于哪一种的 -newkid(newkid); 2007-5-24 (#3697243@0)

对，是应该有一个column 表明answer属于那一种打tata type 谢谢 -howcanido_helpme(howcanido); 2007-5-25 (#3698476@0)

In my humble opinion, you are on the right track. Varchar is used to store strings of variable length. It uses two additional bytes to record the length of the string. -925(Eternal Flame); 2007-5-29 (#3708509@0)
You can design database table as the following:. -whygh(studing); 2007-5-29 {679} (#3708601@0)

BTW, you need one more column in tblquestionAnswer, "SpecifiedAnswer text" to save user specified answer, not from the tblanswer -whygh(studing); 2007-5-29 (#3708609@0)
你这设计只是弄了一个题库。答卷本身呢？回答问题的人呢？ -newkid(newkid); 2007-5-29 (#3708772@0)

my design have problem. It should have another table : recordID int, SurveyID,QuestionID,answerID,specifiedAnswer. the previous table "tblQuestionAnswer" changed to questionID,answerID.add another table named "tblSurveyQuestion" including SurveyID,QuestionID. -whygh(studing); 2007-5-29 {76} (#3708794@0)
I think for this question, there are at least 6 tables as following. please let me know if mydesign still have problem.
1. tblSurvey , table structure: surveyid, survey_desc..
2.tblQuestion ,table structure: questionid, question
3.tblAnswer ,table structure: answerid, answer
4.tblAnswerType ,table structure; answertypeId, Answertype
5.tblsurveyQuestion:,table structure: surveyID, questionid
6.tblquestionAnswer ,table structure: RecordId,surveyID,questionID,answerID,specifiedAnswer -whygh(studing); 2007-5-29 {399} (#3708814@0)

tblAnswer: 没有说明是哪个问题的答案？tblAnswerType和其他表完全没有关系。tblquestionAnswer不清楚你想作什么用，是想存用户答案呢，还是想存问题和答案的关系？ -newkid(newkid); 2007-5-29 (#3708885@0)

Don't make it complicated.1. If you have to store Int answer to an int column, OneTable: questionID, question, intAnswer int nullable, CharAnswer varchar(5000) nullable.
If intAnswer is null, then the answer is in CharAnswer column otherwise the answer is in intAnswer column.

2. If you can store Int answer to a varchar column, OneTable: questionID, question, Answer varchar(5000). -looi500(looi); 2007-5-30 {360} (#3709080@0)

@Florida