-1

I am making a database with 3 tables.

tables(columns):-

people(pid, name, contact),

questions(qid, question),

answers(pid REFERENCE pid(people), qid REFERENCE qid(questions), a, b, c, d)

I just want to know, can i make A,B,C,D as boolean? if person select A so A = TRUE and B,C,D = FALSE(automatically)

a little mentoring will be appreciated, as i dont know what should i look for.

1

Denormalized method (do not do this)

First, we create the first two tables

CREATE TABLE people    ( pid serial PRIMARY KEY, name text, contact text );
CREATE TABLE questions ( qid serial PRIMARY KEY, question text );

One method of doing this is just expanding the check statement to satisfy all permissible variants.

CREATE TABLE answers (
  pid       int  REFERENCES people,
  qid       int  REFERENCES questions,
  option_a  bool NOT NULL DEFAULT false,
  option_b  bool NOT NULL DEFAULT false,
  option_c  bool NOT NULL DEFAULT false,
  option_d  bool NOT NULL DEFAULT false,
  PRIMARY KEY ( pid, qid ),
  CHECK (
       (    option_a AND NOT option_b AND NOT option_c AND NOT option_d)
    OR (NOT option_a AND     option_b AND NOT option_c AND NOT option_d)
    OR (NOT option_a AND NOT option_b AND     option_c AND NOT option_d)
    OR (NOT option_a AND NOT option_b AND NOT option_c AND     option_d)
  )
);

That can be simplified a bit.

CREATE TABLE answers (
  pid       int  REFERENCES people,
  qid       int  REFERENCES questions,
  option_a  bool NOT NULL DEFAULT false,
  option_b  bool NOT NULL DEFAULT false,
  option_c  bool NOT NULL DEFAULT false,
  option_d  bool NOT NULL DEFAULT false,
  PRIMARY KEY ( pid, qid ),
  CHECK (
       (    option_a AND NOT   option_b AND NOT option_c   AND NOT option_d)
    OR (NOT option_a AND (     option_b AND NOT option_c   AND NOT option_d)
                     OR  ( NOT option_b AND (     option_c AND NOT option_d)
                                        OR  ( NOT option_c AND     option_d)))
  )
);

Alas, none of this is normalized. To normalize this design just do this..

CREATE TABLE answers (
  aid           serial PRIMARY KEY,
  description   text
);
CREATE TABLE question_answers (
  pid    int  NOT NULL REFERENCES people,
  qid    int  NOT NULL REFERENCES questions,
  aid    int  NOT NULL REFERENCES answers,
  value  bool NOT NULL DEFAULT false,
  PRIMARY KEY (qid, aid, pid)
);

Normalized Method

Now, if you wish to stop more than one value from being true in the above you can use a PARTIAL UNIQUE INDEX

CREATE UNIQUE INDEX
  ON question_answers(pid,qid,aid,value)
  WHERE value = true; -- (or just WHERE value)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.