Thread: Database table design

Well, off the top of my head here are my thoughts:

You'll want a client table that contains your client data, and this would related to your clientSurvey table on the client id (or whatever primary key you choose in the client table).

Then you'll need a survey table that contains one survey ID for each survey and details about that survey like subject and date etc.

You may want a many to many table of clients and surveys (say a clientSurvey table).

You'll need a Question table. Each new question gets an ID number.

Finally you may want a many to many table to contain the relationships between the Questions and the Survey they belong to. This might be called a SurveyQuestion table.

Just my thoughts. Obviously you need to work out the design to meet your needs.

Zaheir:

I think you really need to understand normalization of data in order to design the proper structure. It is obvious from your post that you do not.

I would suggest reading this and trying to understand it before going further:
Data Normalization

Here's an example of a survey database:

Surveys
SurveyID - int - PK
Title - varchar(100)
Description - varchar(255)
DateCreated - datetime

Questions
QuestionID - int - PK
SurveyID - int (relationship with Surveys table)
Question - varchar(255)

ClientAnswers
ClientAnswerID - int - PK
SurveyID - int (relationship with Surveys table)
QuestionID - int (relationship with Questions table)
ClientID - int (relationship with Clients table)
Answer (depending on the type of questions, this could be an int field used as a relationship to a possible answers table if using a multiple-choice-type survey)
EntryDate - datetime

This is just an idea...and depending on the type of questions the survey allows, it could be much different.

Originally Posted by Zaheir

I would like to ask shouldnt every table have a primary key in sql server if so how do add an auto number to sql server

Typically, tables should have a field that holds a unique value. However, there are cases where it is not necessary. For example, if you had a table for users and a table for usergroups, you would have a third table to link multiple users to multiple groups. This third table would not need a primary key.

In SQL Server, to add an "auto number" field, simply set the "Identity Specification" property to "Yes" and under that, set "(Is Identity)" to "Yes". You also have options to set how much the identity should increment and the seed value.

Where is the db schema?

Have you thought about using foreign keys that relate primary keys,. that would appear to be necessary in this example.