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?"
-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

You have not provided any background information or any functional details
of the business model and rules. Without that others cannot meaningfully
comment on a specific logical design.

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.

Caveat: Database design is the translation of a business model into a
logical schema (a set of tables, views etc. including the integrity and
manipulative rules) in a DBMS. To do this correctly one must have a detailed
and thorough understanding of the business segment that is to be represented
in the database. And for this reason, in general, it is not a great idea to
seek design advice though newsgroups since nobody else in this newsgroup is
aware of your business model and communication may not be sufficiently
effective. In many cases, a specific portion of a design problem may well be
related to the rest of the database schema and may affect other systems/
modules/ infrastructure etc. The advice which one receives here may be based
on what others perceive as the problem and could possibly be wrong with
regard to the overall business requirements.

Based on your narrative, it looks like at least two many-to-many
relationships, one from language entity type to answer entity type and
another from group entity type to answer entity type -- these two jointly
form two distinct dependencies than can only be solved by decomposing it
into a set of minimum three tables.

Theory texts call two or more such relationships dependent on a single
entity type over two or more sets of instances of another entity type as
join dependency. The solution schema obtained after the non-loss
decomposition is often refered to as projection join normal form -- also
known as 5NF.

Since you have not posted you original set of entities let us identify the
entity types involved: Questions, Answers, Languages, AnswerGroups, and so
forth

-- one row per question
CREATE TABLE Questions (
     question_id  INT NOT NULL PRIMARY KEY,
     question  VARCHAR(40) NOT NULL );
INSERT Questions SELECT 1, 'Do you have a dog in your appartment?' ;
INSERT Questions SELECT 2, 'Do you have a dog in your house?' ;
.....

-- one row per language,
CREATE TABLE Languages (
     language_id  CHAR(2) NOT NULL PRIMARY KEY,
     language  VARCHAR(20) NOT NULL ) ;
INSERT Languages SELECT 'en', 'English' ;
INSERT Languages SELECT 'fr', 'French' ;
.....

-- one row per base answer
CREATE TABLE Answers (
     answer_id  INT NOT NULL PRIMARY KEY );
INSERT Answers SELECT 1 ;
INSERT Answers SELECT 2 ;
.....

-- one row per answer per question
CREATE TABLE QuestionAnswers (
     question_id INT NOT NULL
      REFERENCES Questions ( question_id )
      ON UPDATE CASCADE
      ON DELETE CASCADE,
     answer_id INT NOT NULL
      REFERENCES Answers ( answer_id )
      ON UPDATE CASCADE
      ON DELETE CASCADE,
     PRIMARY KEY ( question_id, answer_id ) ) ;
INSERT QuestionAnswers SELECT 1, 1 ;
INSERT QuestionAnswers SELECT 1, 2 ;
INSERT QuestionAnswers SELECT 2, 1 ;
INSERT QuestionAnswers SELECT 2, 2 ;
.....

-- one row per language per answer, a surrogate used for simplicity.
CREATE TABLE LanguageAnswers (
     response_id INT NOT NULL PRIMARY KEY,
     answer_id  INT NOT NULL
      REFERENCES Answers ( answer_id )
      ON UPDATE CASCADE
      ON DELETE CASCADE ,
     language_id CHAR(2) NOT NULL
      REFERENCES Languages ( language_id )
      ON UPDATE CASCADE
      ON DELETE CASCADE
     UNIQUE (answer_id, language_id ))
INSERT LanguageAnswers SELECT 1, 1, 'en' ;
INSERT LanguageAnswers SELECT 2, 1, 'fr' ;
INSERT LanguageAnswers SELECT 3, 2, 'en' ;
INSERT LanguageAnswers SELECT 4, 2, 'fr' ;
.....

-- one row per response of an answer per group
CREATE TABLE AnswerGroups (
     answer_id INT NOT NULL
      REFERENCES Answers ( answer_id )
      ON UPDATE CASCADE
      ON DELETE CASCADE ,
     group_id INT NOT NULL,
     response VARCHAR(10) NOT NULL )
INSERT AnswerGroups SELECT 1, 1, 'Yes' ;
INSERT AnswerGroups SELECT 2, 1, 'No' ;
INSERT AnswerGroups SELECT 1, 2, 'Y' ;
INSERT AnswerGroups SELECT 2, 2, 'N' ;
.....

Now the query is nothing but a simple join:

SELECT q1.question, g1.response
  FROM Questions q1
INNER JOIN QuestionAnswers a1
    ON q1.question_id = a1.question_id
INNER JOIN LanguageAnswers l1
    ON l1.answer_id = a1.answer_id
INNER JOIN AnswerGroups g1
    ON g1.answer_id = l1.answer_id
WHERE q1.question_id = 1
   AND l1.language_id = 'en'
   AND g1.group_id = 1 ;

PS: Re-read the intial paragraph again regarding design advices online. A
qualified professional who has direct access to your business model is
definitely worth it, in case this is a critical project.

Mark (mark.ru***@gmail.com) writes:

It's not clear from the diagram, in which direction you want the relation
to go. If my guess is right, you need to add QuestionGroupID to the tables
QuestionAnswerName and QuestionNames. Really what QuestionAnswer relates
to is not clear. When I think of it, maybe QuestionAnswer should have an
FK to QuestionGroupDetails, and thus also have a QuestionGroupID. And
QuestionAnswerName should relate to QuestionAnswer, not
QuestionGroupDetails.

"Est-ce que vous avez un chien dans votre appartement?" :-)

I fail to see what the proc has to do with the data model.


And next time, please do not post the same question independently to
multiple newsgroups.

VIEW's ORDER BY columns necessary in column list
sql query - sp
FOREIGN KEY that REFERENCES a server login for a database?
connect to oracle database
User-defined function not show up after being created