Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Question Big Survey Database Implementation Design Recomandations

    Hello everyone, I'm a 2nd year student in Data Processing Engineering and I have recently started a side project involving a survey database for an health care center running on a MySQL server and managed by a custom software.

    I did made many research and read many post and articles on survey databases in and outside this forum witch helped me extensively, but I still need some recommendations. I do have some specials element in my specific implementation that would need review from you guys.

    Here is a descriptions of the project:

    -Surveys with Questions.
    -3 types of Answers (Yes/No, Full Answers, Scaled).
    -Many Answer Options will be available for Scaled.
    -Answers will be coming from multiple sources (Kiosk, Web Site, Tablets & Scanned*)
    *I made a software that can get the answers from a scanned survey page and send them to the DB.
    -Anything and everything must be filterable so that the software can create any kind of reports.
    -Multiple Questions can be assigned to multiple Surveys.
    -Multiple Surveys can be assigned to multiple Sources.

    I would need recommendations on:
    -Variable types.
    -Tables & liaisons to them.
    -Overall design.

    Here is my SQL:

    Code:
    CREATE DATABASE surveydb;
    
    CREATE TABLE Questions
    (
    questionID INT NOT NULL AUTO_INCREMENT,
    question VARCHAR(255) NOT NULL,
    type ENUM("Yes/No", "Full Answers", "Scaled"),
    CONSTRAINT Questions_pk PRIMARY KEY (questionID)
    );
    
    CREATE TABLE Sources
    (
    name VARCHAR(255) NOT NULL,
    CONSTRAINT Sources_pk PRIMARY KEY (name)
    );
    
    CREATE TABLE Surveys
    (
    name VARCHAR(255),
    CONSTRAINT Surveys_pk PRIMARY KEY (name)
    );
    
    CREATE TABLE Options
    (
    name VARCHAR(255),
    CONSTRAINT Options_pk PRIMARY KEY (name)
    );
    EDIT:Changed option var to name, option is a mysql reserved word. *Not changed in the diagram image.
    
    CREATE TABLE SurveysQuestions
    (
    question INT NOT NULL,
    survey VARCHAR(255) NOT NULL,
    CONSTRAINT SurveyQuestions_pk PRIMARY KRY (question, survey),
    CONSTRAINT SurveysQuestionsQ_fk FOREIGN KEY (question) REFERENCES Questions(questionID),
    CONSTRAINT SurveysQuestionsS_fk FOREIGN KEY (survey) REFERENCES Surveys(name)
    );
    
    CREATE TABLE QuestionsOptions
    (
    question INT NOT NULL,
    option VARCHAR(255) NOT NULL,
    CONSTRAINT SurveyQuestions_pk PRIMARY KRY (question, option)
    CONSTRAINT QuestionsOptionsQ_fk FOREIGN KEY (question) REFERENCES Questions(questionID),
    CONSTRAINT QuestionsOptionsO_fk FOREIGN KEY (option) REFERENCES Options(option),
    );
    
    CREATE TABLE SourcesSurveys
    (
    source VARCHAR(255) NOT NULL,
    survey VARCHAR(255) NOT NULL,
    CONSTRAINT SurveyQuestions_pk PRIMARY KRY (source, survey)
    CONSTRAINT SourcesSurveysSO_fk FOREIGN KEY (source) REFERENCES Sources(name),
    CONSTRAINT SourcesSurveysSU_fk FOREIGN KEY (survey) REFERENCES Surveys(name),
    );
    
    
    CREATE TABLE Answers
    (
    answerID INT NOT NULL AUTO_INCREMENT,
    question INT NOT NULL,
    answer VARCHAR(255) NOT NULL,
    survey VARCHAR(255) NOT NULL,
    source VARCHAR(255) NOT NULL,
    answerDate DATETIME NOT NULL,
    CONSTRAINT FullAnswer_pk PRIMARY KEY (answerID),
    CONSTRAINT FullAnswerQuestion_fk FOREIGN KEY (question) REFERENCES Questions(questionID),
    CONSTRAINT FullAnswerCenter_fk FOREIGN KEY (center) REFERENCES Centers(name)
    );
    Here is a diagram of it:
    Click image for larger version. 

Name:	SurveyV3.png 
Views:	4 
Size:	32.0 KB 
ID:	14640

    As you can see this is almost done but by lack of experience I wanted to get some help.

    Thank you very much to anyone that is willing to help.
    Last edited by Sytec373; 07-23-13 at 22:52.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    We can't really partner with you over the forum to do your project for free.
    You need to ask specific, targeted questions to get quality responses. "Finish my database design" is unlikely to elicit productive assistance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2013
    Posts
    2

    Very Well

    Ok, I understand. From my point of view, the database is finish I was just looking for some unspotted normalization mistakes and small thing like that and not a "make my work for me please", but its ok, I got some specific questions if someone is willing to help.

    1. In many of my tables like "SurveysQuestions", I'm using 2 variables that are both foreign keys and a composite primary key as a linking table. Is that common or should I be using an AUTO INT ID as a primary key?

    2. In other tables like "Surveys", I only have 1 variable witch is the primary key. Should a AUTO INT ID be used or is the VARCHAR(255) considered ok.

    Keep in mind that I'm trying make this the right way including any good practice elements and conventions as possible. Thanks again for your quick reply.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "I'm using 2 variables that are both foreign keys and a composite primary key as a linking table"
    That doesn't really make sense. Variables? Do you mean columns? And a primary key is not a table. I'm having trouble understanding your question.

    I prefer using identities as primary keys on all my tables, for consistency of application development. It can also improve performance, as a 255 character text field makes a poor primary key. Especially if you have to repeat it as a foreign key like you do with SurveyQuestion.

    Your SurveyQuestions table is a simple "many-to-many" join. You can use the two columns as a primary key, or you can implement an independent surrogate key and put a unique index on the two foreign keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •