Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- You should determine which are the most fitting
- -- data types and sizes for all your table columns
- -- depending on your business context characteristics.
- -- As one would expect, you are free to make use of
- -- your preferred (or required) naming conventions.
- CREATE TABLE Person (
- PersonId INT NOT NULL,
- FirstName CHAR(30) NOT NULL,
- LastName CHAR(30) NOT NULL,
- GenderCode CHAR(3) NOT NULL,
- BirthDate DATE NOT NULL,
- CreatedDateTime DATETIME NOT NULL,
- --
- CONSTRAINT Person_PK PRIMARY KEY (PersonId),
- CONSTRAINT Person_AK UNIQUE (
- FirstName,
- LastName,
- GenderCode,
- BirthDate
- )
- );
- CREATE TABLE Survey (
- SurveyNumber INT NOT NULL,
- Description CHAR(255) NOT NULL,
- CreatedDateTime DATETIME NOT NULL,
- --
- CONSTRAINT Survey_PK PRIMARY KEY (SurveyNumber),
- CONSTRAINT Survey_AK UNIQUE (Description)
- );
- CREATE TABLE PersonSurvey (
- PersonId INT NOT NULL,
- SurveyNumber INT NOT NULL,
- RegisteredDateTime DATETIME NOT NULL,
- --
- CONSTRAINT PersonSurvey_PK PRIMARY KEY (PersonId, SurveyNumber),
- CONSTRAINT PersonSurveyToPerson_FK FOREIGN KEY (PersonId)
- REFERENCES Person (PersonId),
- CONSTRAINT PersonSurveyToSurvey_FK FOREIGN KEY (SurveyNumber)
- REFERENCES Survey (SurveyNumber)
- );
- CREATE TABLE Question (
- QuestionNumber INT NOT NULL,
- Wording CHAR(255) NOT NULL,
- CreatedDateTime DATETIME NOT NULL,
- --
- CONSTRAINT Question_PK PRIMARY KEY (QuestionNumber),
- CONSTRAINT Question_AK UNIQUE (Wording)
- );
- CREATE TABLE SurveyQuestion (
- SurveyNumber INT NOT NULL,
- QuestionNumber INT NOT NULL,
- PresentationOrder TINYINT NOT NULL,
- IsMandatory BIT NOT NULL,
- IntegratedDateTime DATETIME NOT NULL,
- --
- CONSTRAINT SurveyQuestion_PK PRIMARY KEY (SurveyNumber, QuestionNumber),
- CONSTRAINT SurveyQuestion_AK UNIQUE (
- QuestionNumber,
- SurveyNumber,
- PresentationOrder
- ),
- CONSTRAINT SurveyQuestionToSurvey_FK FOREIGN KEY (SurveyNumber)
- REFERENCES Survey (SurveyNumber),
- CONSTRAINT SurveyQuestionToQuestion_FK FOREIGN KEY (QuestionNumber)
- REFERENCES Question (QuestionNumber)
- );
- CREATE TABLE Response (
- SurveyNumber INT NOT NULL,
- QuestionNumber INT NOT NULL,
- PersonId INT NOT NULL,
- Content TEXT NOT NULL,
- ProvidedDateTime DATETIME NOT NULL,
- --
- CONSTRAINT Response_PK PRIMARY KEY (SurveyNumber, QuestionNumber, PersonId),
- CONSTRAINT ResponseToPersonSurvey_FK FOREIGN KEY (PersonId, SurveyNumber)
- REFERENCES PersonSurvey (PersonId, SurveyNumber),
- CONSTRAINT ResponseToSurveyQuestion_FK FOREIGN KEY (SurveyNumber, QuestionNumber)
- REFERENCES SurveyQuestion (SurveyNumber, QuestionNumber)
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement