Immediate help!!! User Responses and Database Table updation

Posted by Sina under Sql Server on 5/31/2012 | Points: 10 | Views : 794 | Status : [Member] | Replies : 2
Hi..i have a doubt..I have a designed a page which is a kind of blog..i.e there are few topics and users can respond to the topics.My doubt is there a way to store user responses into database to retrieve later and also how can i store the responses for a particular topic corresponding to the title?Since there are other topics too and also how can we define the field to store responses since it is not predictable as to how many users will respond....Hope i am clear...Please help me..Thanks in advance..

@Sina


Responses

Posted by: Ksuresh on: 5/31/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

By using AutoId of title you solve above problem and for different topics give Topic Id so that for which topic response you can differentiate , Hope this helps you

Regards
Suresh

Sina, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 5/31/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Please follow the sample Tables/Data

1. Topic Related Information
IF OBJECT_ID('Tbl_Topics') IS NULL

Create Table Tbl_Topics
(
TopicID INT IDENTITY(1,1) PRIMARY KEY,
TopicName VARCHAR(100)
)
go
2. Questions on Topics/Multiple Topics
IF OBJECT_ID('Tbl_Questions') IS NULL

Create Table Tbl_Questions
(
QuestionID INT IDENTITY(1,1) PRIMARY KEY,
Question VARCHAR(500),
TopicID INT FOREIGN KEY REFERENCES Tbl_Topics(TopicID) NOT NULL,
PostedOn DATETIME,
PostedBy INT
)
go
3. Responses on various Questions
IF OBJECT_ID('Tbl_Responses') IS NULL

Create Table Tbl_Responses
(
ResponsesID INT IDENTITY(1,1) PRIMARY KEY,
DetailResponse VARCHAR(MAX),
QuestionID INT FOREIGN KEY REFERENCES Tbl_Questions(QuestionID) NOT NULL,
ResponseDate DATETIME,
ResponseBy INT
)
go
4. Sample Data for Topics, Questions and Responses
INSERT Tbl_Topics(TopicName) VALUES('SQL Server')

INSERT Tbl_Topics(TopicName) VALUES('ASP.Net')
Go

INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Where we use Collation ?',1,GETDATE(),1001)
INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Can we force Referential Integrity on Temp tables ?',1,GETDATE(),1002)
INSERT Tbl_Questions(Question,TopicID,PostedOn,PostedBy) VALUES('Why should we use "App.Copnfig" ?',2,GETDATE(),1001)
Go

INSERT Tbl_Responses(DetailResponse,QuestionID,ResponseDate,ResponseBy) VALUES('To force Sensitivity on Data(i.e: Case, Accent, width)',1,GETDATE(),1010)
Go
5. Query to fetch What are all the Questions have got responses on which Topics
SELECT T.TopicName,Q.Question, Q.PostedBy, Q.PostedOn,R.DetailResponse, R.ResponseDate, R.ResponsesID

FROM Tbl_Responses R JOIN Tbl_Questions Q
ON (R.QuestionID = Q.QuestionID)
JOIN Tbl_Topics T
ON (T.TopicID = Q.TopicID)
ORDER BY T.TopicName, Q.Question, R.ResponseDate DESC
Go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sina, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response