Table design request for comment. (Help please)

Discussion in 'microsoft.public.sqlserver' started by Mark, Jul 14, 2006.

  1. Mark

    Mark Guest

    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
     
    Mark, Jul 14, 2006
    #1
    1. Advertisements

  2. Mark

    mark.rullo Guest

    Posted in another group, hope this further explains what we need.
    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.
     
    mark.rullo, Jul 14, 2006
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.