Hi there gurus, can you please add your 2 cents on this design? 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. If you
folks would be so kind as to add your comments to the design I would be
truely grateful.


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

Posted in another group, hope this further explains what we need.
[quoted text, click to view]

I'll try and expand on the business requirements as requested.

Our goal in all of this 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) (see above example).

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 the above example, we'd
pass in a groupID and a LanguageID into a proc and 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

I think the trick is not to put the LanguageID in the groupDetails
table so that the LangID can just be passed into the proc, and the
appropriate question / answer for that language is returned.

I hope this helps with the requirements.