Tables creation

Question

Hi Experts,

I need to create tables to handle the below requirement :

1.Need to handle forms data from portal which contains a set of fields specific to question and answers.

For example : Portal page will have a lot of text box for each and every question. The questions can range from 150 to 600 plus.

2.The form also contains other information like the user details like Name,address,age

Now what i am thinking

1. Create a table for storing generic information about the User.

2. Create a table for the Questions as it will be shared for different user sessions.

Now am actually confused on how to design the table to store the answers and relate it with questions and user details.

Do we need to create a table for answers that will support multiple inserts. 1 insert for each answer and keyed questionID and UserID or some other better approach is also possible so my inserts are optimized as its expected more than 1000 plus user fill up the form .

Please suggest as the previous dev added columns for each question and messed up the design.

Thanks

Priya

Answer 1

I think that this solution help you.

1) Users table: Record users data.

2) Questions table: Prefixed questions of your form, with a record to each question, with ID and DESCRIPTION.

3) RecordQuestionUser table: Storage data linking info about User and Question (The value must be 0, 1 or NULL (in case you dont know 150 or 600 questions)).

CREATE TABLE [dbo].[Users]
(
	[IdFuncionario] [int] PRIMARY KEY,
	[Name] [varchar](50) NOT NULL,
	[address] [varchar](50) NULL,
	[age] INT NULL	
) 

CREATE TABLE [dbo].[Questions]
(
	[IdQuestion] [int] PRIMARY KEY,
	[QuestionDescription] VARCHAR (20),
)

CREATE TABLE [dbo].[RecordQuestionUser]
(
	[IdRecord] [int] IDENTITY(1,1) PRIMARY KEY,
	[IdUser] [int]  REFERENCES [dbo].[Users],
	[IdQuestion] INT REFERENCES [dbo].[Questions],
	[RecordResult] [INT] 
)

It help you?

Answer 2

Something like this should work:

DECLARE @users TABLE (userID INT, userName NVARCHAR(30))

DECLARE @questions TABLE (questionID INT, questionText NVARCHAR(200))

DECLARE @answers TABLE (questionID INT, userID INT, answer NVARCHAR(200), answerSequence INT, answerDateTime DATETIME)

Everything is stored as it's own entity. Questions can be reused in any order, and the answers can be recorded in that order, and sequenced again later.

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

Answer 3

Hi Priva

I must start with saying that I do not think that it is a good idea to design your database based on the forum. A database architecture is something much more complex than a simple question. To get the right architecture you must have full understanding of the business logic, full understanding on needs, agendas, and goals, but much more, you must have full understanding of any related system (directly or indirectly related), and more. Personally I will not give advice regarding what fit your specific need :-(

With that said, I can offer some points that can help you get the decision :-)

1. As first step, I recommend to think about the entities that will take part in the application. For example you mentioned "users", "questions", and "answers". These might be the basic logic of the database, but don't be hasty.

2. Next, go deeper and make sure that you understand the way people will use these entities (Think about services or modules or APIs in the application). Now go deeper to each entity... For example, is your "answers" are free text or the user need to choose from a list of available answers? or maybe both options. In the first option the "answers" can be stored in "Answers" table and the application should show the answers that related to the specific question. In the case of free text, you should ask your self what type of "free text" should it be? is it number for example or string (the question "what is your age" must return integer, while the question "please write your comment" will use string). 

3. Next you need to think about the relations between the entities. For example in case of available answers the Question table is simply related to the Answers table in One-to-Many using foreign key, but in case of free text you must store the input in another table (these are not the same as the Answers table). You should think about using another table which store the user responses, so we have "available answer" table and "user answers" table (Don't use these names! I am just talking about the logic). The "user answers" table should be related to the Users table with many to many relations if we are using available answers, but should be related to the User table with one-to-many for free text ,therefore maybe the free text should be stored in another table?!? As you can see there is a lot to think about and this is not even the starting point :-)

* as I mentioned if your system need to point to Enterprise level use it might be important to think about the type of the free text answers as well! In small systems some people simply store all free text in NVARCHAR type... I do not recommend this, since statistics and processing the answers is an important factor in the system and can be heavy performance if not well designed  (now read a bit about using sql_variant type... I AM NOT SAYING THAT YOU SHOULD USE IT necessarily in your case, but reading about this will leads you to other solutions as well and you will be able to choose).

4. You are talking about users entities. will you have an admin to the application? probably you will. will you have people that create these Questions/Answers ? probably yes... Should you create table for each type of users?!? Most likely NO! You should not think about "users" as the people that fill the form but as users of the system. You will probably need Full users system. Check this new article in the TechNet WIKI, it is perfect as starting point:  ASP.NET MVC 5 Security And Creating User Role. 

In summary

using only simple Users, Questions, and Answers tables is HIGHLY NOT recommended by me, and I think it will not give a flexible solution (but these are the minimal options that people can offer you probably).

* If this is interesting I can continue... please inform me if I am helping you or just confusing you.
I just don't want to wast time, and it took me long time to write this :-)

* I developed a nice questionnaires system which is used by many companies including universities and the Ministry of Education in Israel. It give the you option to create a full online exams (more then 50 different types of questions in some cases, and more can be added). You can see some screenshot of one version here: http://priza.co.il/index.aspx?KPageId=113

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]