Design Question?

RangerGuy posted on Friday, July 14, 2006

Hi everybody, I know this is not a CSLA question but everybody always offers such great advice on this board I just had to ask here as well :) If you don't mind offering some suggestions on the following we'd really appreciate it.

We're having trouble relating these tables in a diagram because of the keys.
Is it necesary to have the references setup? 
I would assume yes so the forign keys can be setup.

If you look at this link, you'll see our diagram. 
In Red are the relationships that we would like to make for referential integrity, but cannot because of the keys.
http://rullo.ca/linktome/QuestionsDB.jpg

Our goal in all of this is to have a facility wherin we can store a question, that has multiple names over multiple Languages.  For instance:
 -Q1| QNameID = 1 | "Do you have a dog in your appartment?"  | LangID = 1(eng)
 -Q1| QNameID = 2 | "Do you have a dog in your house?"   | LangID = 1(eng)
 -Q1| QNameID = 1 | "-French - Do you have a chien in your appartment?"  | LangID = 2(fr)
 -Q1| QNameID = 2 | "-French - Do you have a chien in your house?"   | LangID = 2(fr)

The difficulty is when we try and put this in the group details table.  We don't want to outline the Language, we'd just pass the language into a proc to retreive a specific group with a specific language.  

CREATE TABLE [Question] (
 [QuestionID] [int] NOT NULL ,
 [SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 CONSTRAINT [PK_Question] PRIMARY KEY  CLUSTERED
 (
  [QuestionID]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [QuestionAnswer] (
 [QuestionID] [int] NOT NULL ,
 [QuestionAnswerID] [int] NOT NULL ,
 [SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY  CLUSTERED
 (
  [QuestionID],
  [QuestionAnswerID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY
 (
  [QuestionID]
 ) REFERENCES [Question] (
  [QuestionID]
 )
) ON [PRIMARY]
GO

CREATE TABLE [QuestionAnswerName] (
 [QuestionAnswerID] [int] NOT NULL ,
 [QuestionAnswerNameID] [int] NOT NULL ,
 [LanguageID] [int] NOT NULL ,
 [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_QuestionAnswerName] PRIMARY KEY  CLUSTERED
 (
  [QuestionAnswerID],
  [QuestionAnswerNameID],
  [LanguageID]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [QuestionGroup] (
 [QuestionGroupID] [int] NOT NULL ,
 [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 CONSTRAINT [PK_QuestionGroup] PRIMARY KEY  CLUSTERED
 (
  [QuestionGroupID]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [QuestionGroupDetails] (
 [QuestionGroupID] [int] NOT NULL ,
 [QuestionNameID] [int] NOT NULL ,
 [QuestionAnswerNameID] [int] NOT NULL ,
 [QuestionSortOrder] [int] NULL ,
 [AnswerSortOrder] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DisplayLevel] [int] NULL ,
 CONSTRAINT [PK_QuestionGroupDetails] PRIMARY KEY  CLUSTERED
 (
  [QuestionGroupID],
  [QuestionNameID],
  [QuestionAnswerNameID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_QuestionGroupDetails_QuestionGroup1] FOREIGN KEY
 (
  [QuestionGroupID]
 ) REFERENCES [QuestionGroup] (
  [QuestionGroupID]
 )
) ON [PRIMARY]
GO

CREATE TABLE [QuestionNames] (
 [QuestionID] [int] NOT NULL ,
 [QuestionNameID] [int] NOT NULL ,
 [LanguageID] [int] NOT NULL ,
 [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Desciption] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ControlTypeID] [uniqueidentifier] NOT NULL ,
 CONSTRAINT [PK_QuestionNames] PRIMARY KEY  CLUSTERED
 (
  [QuestionID],
  [QuestionNameID],
  [LanguageID]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_QuestionNames_Question] FOREIGN KEY
 (
  [QuestionID]
 ) REFERENCES [Question] (
  [QuestionID]
 )
) ON [PRIMARY]
GO

 

 

xal replied on Friday, July 14, 2006

Just by looking at your diagram (jpg), I believe that you are making your foreign keys part of your primary keys...
I suggest removing your fks from your primary keys when appropiate... for example, QuestionAnswerId could be the primary key in QuestionAnswer and QuestionId would just be a regular field...
Another thing: The QuestionAnswerName appears to be just a localization table for QuestionAnswer.
If that is the case, then there's no point in creating a relation between QuestionAnswerName and QuestionGroupDetails. I'd create a relation between QuestionAnswer and QuestionGroupDetails directly.
Now, being like that, you don't really need a relation between QuestionNames (because it's a localization table) and QuesionGroupDetails. But there's also no need for creating one between QuestionGroupDetails and Question because QuestionGroupDetails already has a relation to QuestionAnswer and that has a relation to Question.

So, your QuestionGroupDetails could look like:
@QuestionGroupId
@QuestionAnswerId
QuestionSortOrder
AnswerSortOrder
DisplayLevel

And it would only relate to QuestionAnswer and QuestionGroup

Andrés

RangerGuy replied on Friday, July 14, 2006

Thank you very much for the suggestion. I think that maybe my above definition of our desired out come is a little confusing. Here is a little clear explanation of what we want.

Our goal is to have a facility wherin we can store questions and the associated answers. We need to have multiple names for these questions and answers over multiple Languages.

For each question ID (one question) we want to be able to ask it or display it differently(n ways)
The same requirement goes for each answer. We want to have multiple "names" for one answer over multiple languages. For instance

QueAnsID = 1 | AnsNameID = 1 | LangID = 1(en) | "Yes"
QueAnsID = 1 | AnsNameID = 2 | LangID = 1(en) | "Y"
QueAnsID = 1 | AnsNameID = 1 | LangID = 2(fr) | "Oui"
QueAnsID = 1 | AnsNameID = 2 | LangID = 2(fr) | "O"

QueAnsID = 2 | AnsNameID = 3 | LangID = 1(en) | "No"
QueAnsID = 2 | AnsNameID = 4 | LangID = 1(en) | "N"
QueAnsID = 2 | AnsNameID = 3 | LangID = 2(fr) | "Non"
QueAnsID = 2 | AnsNameID = 4 | LangID = 2(fr) | "N"

This is so that when it comes together from teh above example, we'd pass in a groupID and a LanguageID into a select statementand the result would be:

Do you have a dog in your appartment?
Yes
No

Or, if the group were different:

Do you have a dog in your house?
Y
N

Were having some problems normalizing this data. Here is a link to an image of the table structure we came up with.
We are trying to leave the LanguageID out of the group details so that we don't have data duplicated that doesn't need to be.

So we can have 1 group of Questions/Answers but have a choice to display it in mulitiple languages AND so that a report could have abbreviated question name and answers but still have the same value in the database.

xal replied on Friday, July 14, 2006

RangerGuy,

So, let's see if I got it straight:

You're going to have 1 row in QuestionAnswerName for combination between QuestionAnswer and LanguageId, right?

If that is the case, then I understood correctly and you need not join QuestionAnswerName and QuestionGroupDetails and my recommendation still applies.

Now, if what you're saying is that one combination of QuestionAnswer and LanguageId can exist more than once ( !!! ) then you'll need to create a new table to have the languageId and questionanswernameid and the actual text. But I don't want to dive into a more thorough explanation because I don't think that's what you want...

If you are only going to show one language at a time, then think about what I said in my first post, because I think that is the appropiate way to handle it. The way I see it, the xxxxName tables are only there to provide localization for Question and QuestionAnswer, so you should only join QuestionGroupDetails to QuestionGroup and QuestionAnswer. I've worked in two projects of the same characteristics and that's the way it was handled in both.

Andrés

david.wendelken replied on Friday, July 14, 2006

I like to start with the true definition of things.  Once I understand that, the model I need is usually pretty obvious (or the model I have is usually pretty obviously wrong :).

I'm not trying to model this for you, I'm trying to identify concepts you may want to consider in your model.  You'll have to evaluate them against your business needs.

I'm doing this because the meanings behind some of your tables seem a bit jumbled to me.  Doesn't mean that they are, it could be that I don't have the context to understand your intent.

I'm guessing that QuestionGroup serves the purpose of grouping questions into a survey of some sort.  Change the lingo as needed. :)

I've chosen to supply a sample model using the following definitions using mostly composite primary keys rather than identity columns for each table.  Why?  Because I'm leaving the office early and it saves on typing!  Convert to identity columns if that's your style. :)

Hope this helps!

Question: 

A record of the existence of a question of interest to the organization. 

CultureLanguage:

A language / culture combination that would affect how questions are asked, and in which we want to ask (or have asked) questions.  I.e., French is used by French nationals and French-Canadians.We might want to phrase the question differently to the two groups due to dialect differences.  (Technical note: one could argue this is two things, but I suspect that's overkill for your purposes.) 

QuestionText:

The actual text of a question in a specific language aimed at a specific culture.

Allowable Answer:

A record of the existence of a pre-defined answer to a question, as in a choice in a multiple choice question.  This is used to provide a list of choices to the question.

Allowable Answer Text:

The actual text for an allowable answer in a specific language aimed at a specific culture.

Survey 

The existence of a set of questions that have been grouped together.
(This and Survey Question below are my guesstimate of what your QuestionGroup is about.)

Survey Question

The assignment of a question to a survey.  (It is possible that we might want to ask the same question more than once in a given survey.)

Respondent
A person we will ask (or have asked) questions of.

SurveyResponse
A survey posed to a respondent at a specific point in time.  (We might ask them the same set of questions more than once, and need to distinquish between the different sets of answers.)

Survey Question Answer

The actual answer given by a respondent to a survey question, in a specific language and an assumed cultural group. 

(Answers to the same question may be very different, even from the same person answering it, depending upon the questions that were posed before.  Thus, I have modeled a Survey Question answer and not a Question Answer.)

(Example: I've identified a cultural/language group called German.  I record the question text for a question using the German cultural/language.  I ask a man on the street in Germany a question in German, and he answers me in German, so I record his answer as being German.  But he might have been a Turkish guest worker who speaks German.  That's probably beyond your project scope... :)

 

 

========

Question: 

QuestionId    (primary key)
SystemName (unique key)

CultureLanguage:

LanguageId  (primary key)
Name           (unique key)

QuestionText:
QuestionId     (primary key)
LanguageId    (primary key)
QuestionText

Allowable Answer:
AllowableAnswerId (primary key)
Question Id
SystemName (unique key)

Allowable Answer Text:
AllowableAnswerId (primary key)
LanguageId             (primary key)
AllowableAnswerText (unique key)
Survey 
SurveyId (primary key)
SystemName (unique key)

Survey Question
SurveyId     (primary key)
QuestionId  (primary key)
PresentationOrder (primary key)

Respondent
RespondentId (primary key)
Name
CurrentPreferredLanguageId

SurveyResponse
SurveyResponseId       (primary key)
SurveyId                      (unique key 1)
RespondentId              (unique key 1)
DateTimeOfResponse  (unique key 1)
LanguageId  

(Note: Assumes same language for all questions - if you are doing work with immigrants, they might want to be asked in one language first, and only revert to their native language if they don't understand that particular question.  If so, LanguageId needs to be recorded in SurveyQuestionAnswer instead.)

Survey Question Answer
SurveyResponseId (primary key)
QuestionId             (primary key)
AnswerText