Home Forums Programming General question on best way to set up table or procedure RE: question on best way to set up table or procedure

  • When the user has the option of inputting their demographic, can they have more than one? Ideally, when dealing with a many-to-many relationship, you would use a composite key table. In very simple terms:
    CREATE TABLE dbo.Answer (AnswerID int, UserID int);
    CREATE TABLE dbo.Demographic (DemographicID int, DemographicDescription varchar(50));
    GO
    --Now your composite table
    CREATE TABLE dbo.AnswerDemographic (AnswerID int, DemographicID int);
    ALTER TABLE dbo.AnswerDemographic WITH CHECK ADD CONSTRAINT FK_AnswerID FOREIGN KEY (AnswerID)
    REFERENCES dbo.Answer (AnswerID);

    ALTER TABLE dbo.AnswerDemographic WITH CHECK ADD CONSTRAINT FK_DemographicID FOREIGN KEY (DemographicID)
    REFERENCES Demographic (DemographicID);
    GO

    DROP TABLE dbo.Answer;
    DROP TABLE dbo.Demographic;
    GO

    Is my understanding for that correct?

    Then you could do a query along the lines of:
    SELECT COUNT(A.AnswerID) AS Num,
           A.Answer
    FROM dbo.Answer A
      JOIN dbo.AnswerDemographic AD ON A.AnswerID = AD.AnswerID
      JOIN dbo.Demographic D ON AD.DemographicID = D.DemographicID
    WHERE A.QuestionID = 1
    AND D.DemographicDescription = 'Sales Department';

    So you can easily count the answers for specific demographics, and people answering can give all their demographic details, instead of having to choose only one, when many might apply.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk