Many to many table relationship

Question

Many to many table relationship

tracyedmund

Newbie

Points: 3
More actions
August 2, 2010 at 11:20 pm

#224960

Am designing a table to hold data containing students Test that will be uploaded from csv file. I can upload data to Answer, Person and Question Table But cannt retrieve useful information..My table design is below;
CREATE TABLE [dbo].[Answer](
[AnswerID] [int] IDENTITY(1,1) NOT NULL,
[AnswerDescription] [ntext] NULL,
[StudentResponse] [ntext] NULL,
[PossiblePoint] [ntext] NULL,
[AutoScore] [ntext] NULL,
CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED
(
[AnswerID] ASC
CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[ModuleTaught] [nvarchar](50) NULL,
[Department] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[RoleName] [nvarchar](50) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
CREATE TABLE [dbo].[Question](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[QuestionDescription] [nvarchar](max) NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC
CREATE TABLE [dbo].[Test](
[QuestionID] [int] NOT NULL,
[AnswerID] [int] NOT NULL,
[ModuleID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY NONCLUSTERED
(
[QuestionID] ASC,
[AnswerID] ASC,
[PersonID] ASC,
[ModuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
ON UPDATE CASCADE
GO

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

As a rule of thumbs, when you bump into a many-to-many relationship consider adding a bridge table to convert such m-to-m relationship into two 1-to-m.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.