#1
  1. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,861
    Rep Power
    368

    Vouchers storing in db?


    I have a question & choices table

    q(q_id....)
    choices(choice_id, q_id, choice..)

    sometimes when users answers a question, we will send them vouchers regardless of the answer. Other times when they select a particular choice, we will send them a voucher.

    Here is the table i came up with:
    vouchers(voucher, q_id, choice_id, user_id*)

    should there be an intermediate table linking vouchers with q?

    Edit:what about if we want to send voucher if user selects choice 1, choice 3 out of 4...*choices?

    *alternatively dont have user_id, but "remove" a voucher once it has been emailed to the user, keeps the table size in check?
    Last edited by paulh1983; July 18th, 2013 at 07:46 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    Can you elaborate a tiny bit more about your schema? You said you have a q&a table, and then show what looks like two table definitions. I'm assuming this is in an SQL-based db? Seeing the CREATE TABLE commands that make your tables would be helpful.

    That said, I'll assume questions and choices are in different tables. Users would be in a different table as well. A voucher isn't a natural part of a user, nor is it a natural part of a question or choice. It also seems like a voucher doesn't represent a user's interaction with a particular question, but is related to that interaction. So you would wind up with:
    • question
    • choice
    • user
    • answer
    • voucher_ilk
    • voucher

    Questions are just that, questions. Choice has a FK to question. User is, well, users. Answer has a FK to user and a FK to choice an anything else relevant (a date, maybe?) -- if the PK of choice is (question, number) and the PK of answer is (user, question) then you prevent users from multiply answering questions. Voucher has a FK to voucher_ilk, a FK to answer and stores the serial of the voucher, its issue date, and whatever else is relevant -- and if the PK of voucher is just (answer) which might be (user, question) by the concept above, then you guarantee one voucher per answer as well (and reduce the query load to just one to find out what users got what vouchers).

    This way not all answers require a voucher.
    sql Code:
    CREATE TABLE question
     (id        SERIAL PRIMARY KEY,
      body      text NOT NULL);
     
    CREATE TABLE choice
     (question  INTEGER REFERENCES question NOT NULL,
      NUMBER    INTEGER NOT NULL,
      body      text NOT NULL,
      PRIMARY KEY (question, NUMBER));
     
    CREATE TABLE USER
     (uname    VARCHAR(50) PRIMARY KEY);
     
    CREATE TABLE answer
     (USER      VARCHAR(50) REFERENCES USER NOT NULL,
      question  INTEGER NOT NULL,
      NUMBER    INTEGER NOT NULL,
      dtg       timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
      FOREIGN KEY (question, NUMBER) REFERENCES choice (question, NUMBER),
      PRIMARY KEY (USER, question));
     
    CREATE TABLE voucher_ilk
     (code       VARCHAR(20) PRIMARY KEY,
      description text NOT NULL);
     
    CREATE TABLE voucher
     (USER      VARCHAR(50) REFERENCES USER NOT NULL,
      question  INTEGER REFERENCES question NOT NULL,
      code      VARCHAR(20) REFERENCES voucher_ilk NOT NULL,
      PRIMARY KEY (USER, question));

    You might go "Oh noes! Multi-column PKs and stuff!" But there are serious advantages to this. In the event you have a braindead framework that demands a single-column numeric PK called "id" (which is turns a relational system into a 1960's style navigational one... har har) you can easily just add a meaningless one to each table:
    sql Code:
    CREATE TABLE answer
     (id        SERIAL UNIQUE NOT NULL,
      question  INTEGER NOT NULL,
      NUMBER    INTEGER NOT NULL,
      dtg       timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
      FOREIGN KEY (question, NUMBER) REFERENCES choice (question, NUMBER),
      PRIMARY KEY (USER, question));

    Without infecting your system with the dumb and preventing your framework or an external script from merrily entering gobs of bogus data that breaks the rules your organization issues vouchers and permits answers by. Of course, your rules may be significantly different from what the structure above represents, in which case you'll need to do some FK fanagling or table realignment (what if users can make more than one choice? or you need to let them change it, but want to keep a history of their choices over time, etc.), but you get the idea (unless I totally misread the question ^.^ ).
    Last edited by zxq9; July 19th, 2013 at 02:41 AM.

IMN logo majestic logo threadwatch logo seochat tools logo