** UnhandledExceptionEventHandler :: OFFICIAL LC / CHATTER THREAD **

[Neil S]

Originally Posted by gaming_mouse

Neil, you seem like a good programmer, so I am genuinely curious: Whatever you are referring to, how would it affect you practically as, say, a Rails programmer? Or is it just a matter a principle for you?

Well Rails, when it gets bad enough in the past, has just not been available on certain new versions of Ruby. It took them forever to be available on 1.9 at all, if I recall.

So how it would affect me if I used Rails (which I don't really care for) would be that I would be required to keep parallel versions of Ruby on hand for Rails.

Which wouldn't be fun.

[Gullanian]

Sorry bit of shameless self promotion, just finished writing our new web store:
http://www.scirra.com/store

This page is one I'm most happy with:
http://www.scirra.com/store/music-bu...k-prescription

I don't know if anyone else has written a store before? I found it to be one of the most challenging things I've ever done. It loads as much as it can with AJAX as well (namely clicking left menus, adding to cart without page reloading, changing currency without page reloading etc) which makes it very speedy and responsive, but was another layer that complicated things. It should all degrade gracefully as well for non JS browsers (although I haven't extensively tested this!)

One of the hardest parts was the database design, and of course payment handling. The database has some fairly complex relationships in it and doing things like revenue sharing for authors in multiple currencies is a pita!

[gaming_mouse]

Gull,

The whole cart feels really slick -- nice work. i especially love the subtle graphic effects when things change. One tiny tiny nit -- remove the "update" button and just make the cart update as you type a new quantity, or maybe when you leave the form field?

[Colombo]

I'm trying to construct a SQL database that will keep track of survey results.

Here are some finer details:

• A survey has many questions. A single question can be used in many surveys.
• A question has many possible answers. A single answer can be used for many questions.
• One user can do many surveys. One survey has many users.
• We want to keep track of the index of each question in any survey it appears in
• On user can answer a specific question only once, regardless of what survey it is in.

The last bullet point is the one giving me the most trouble.

I've been wrapping my head around this for a few days and I've been trying to design a UML class diagram before constructing this in SQL. Here's what I've got right now:

Spoiler:

I think that I'm very close here, but I'm not a fan of the 'QuestionAnswer' class and think I'm doing something wrong. It just doesn't seem right. I'm not sure it should be considered an association class between an OfferedAnswer and a Question...

Could anyone give me any pointers here? I need to keep track of a user's responses, while also ensuring that each response is uniqued on (userID, questionID).

[tyler_cracker]

Business logic belongs in code. Don't try to handle those complex relationships in your storage layer.

[tyler_cracker]

Also I'm on my phone + lol uml = I didn't look at your drawring too closely.

[gaming_mouse]

Originally Posted by tyler_cracker

Business logic belongs in code. Don't try to handle those complex relationships in your storage layer.

What do you mean? There's no logic there, it's just a db schema.

[gaming_mouse]

Originally Posted by Colombo

Could anyone give me any pointers here? I need to keep track of a user's responses, while also ensuring that each response is uniqued on (userID, questionID).

Not sure I understand the question. Obviously you need a QuestionAnswer table of some sort to store the answers to the questions.

I do think this schema is going to lead to some difficulties in the UI where you create surveys and have the option to choose questions from existing ones, and how you handle the display of questions in a survey where a user has already answered -- do you just skip those? Without knowing why you've decided this question reuse is so important it's hard to evaluate.

[sdturner02]

I've done something very similar to this in the past. This shouldn't be too tough, you just need to use primary keys.

/* holds data for individual surveys */
create table surveys
(
survey_id int unsigned not null auto_increment primary key,
description text not null
)
engine=INNODB;

/*
Table for the text and type of each question.
The qid column has an auto incrementing primary key so each question can be uniquely identified.
*/
create table questions
(
qid int unsigned not null auto_increment primary key,
qtext text not null,
qtype text not null
)
engine=INNODB;

/*
This table creates relationships between surveys and questions. The primary keys of the surveys table and questions table are used as a composite key. This allows a single question to be used on different surveys, but only once.
*/
create table survey_questions
(
survey_id int unsigned not null default 0,
qid int unsigned not null default 0,
primary key (survey_id, qid)
)
engine=INNODB;

/*
Table that holds the answers to each question.The primary key is a composite of the unique userID and qid for each question. This allows each user to only provide an answer for each question once.
*/
create table user_answers
(
userID int unsigned not null default 0,
qid int unsigned not null default 0,
answer_text text not null,
primary key (userID, qid)
)
engine=INNODB;

Also, from your picture it looks like you might be duplicating data. The NumQuestions field contains data that is available elsewhere (that's why I left it out in mine). If you need the # of questions in a survey, why don't you try something like

SELECT count(*) FROM survey_questions WHERE survey_id = current_survey

where "current_survey" is just the survey_id of whichever one you're trying to find the number of questions for.

[sdturner02]

Originally Posted by gaming_mouse

Not sure I understand the question. Obviously you need a QuestionAnswer table of some sort to store the answers to the questions.

I do think this schema is going to lead to some difficulties in the UI where you create surveys and have the option to choose questions from existing ones, and how you handle the display of questions in a survey where a user has already answered -- do you just skip those? Without knowing why you've decided this question reuse is so important it's hard to evaluate.

This can probably be handled pretty easily with a subquery. If you want to pull the questions in a particular survey, but only ones that the user hasn't answered elsewhere, I think this would work:

SELECT questions.*
FROM questions, survey_questions
WHERE questions.qid = survey_questions.qid
AND survey_questions.survey_id = current_survey
AND questions.qid NOT IN (SELECT qid FROM user_answers WHERE userID = current_user)

[gaming_mouse]

Originally Posted by sdturner02

This can probably be handled pretty easily with a subquery. If you want to pull the questions in a particular survey, but only ones that the user hasn't answered elsewhere, I think this would work:

SELECT questions.*
FROM questions, survey_questions
WHERE questions.qid = survey_questions.qid
AND survey_questions.survey_id = current_survey
AND questions.qid NOT IN (SELECT qid FROM user_answers WHERE userID = current_user)

What's hard isn't writing a query to figure out what questions they havn't answered. What's hard is all the UX issues it raises:

Do you silently omit the question?
Do you display it but greyed out with the existing answer which cannot be changed?
Do you just show a message saying Question 6 has been skipped because you already answered it? Or do you just renumber the questions? Etc, etc.

You really have to think hard about your business goals and the user experience to answer questions like this. They might seem trivial and easy, but ime figuring stuff like this out is by far the hardest and most time consuming part of application development.

[sdturner02]

I totally, 100% agree. I thought you were talking about the just the actual DB query.

[n00b590]

I'm running some database calculations using python and MySQL (with SQLAlchemy) and I'm getting REALLY slow database write speeds.. over six hours to write/update 400,000 records. Any idea what could be causing this? I did some googling and didn't find anything useful. I have a fairly powerful computer, 16 GB of RAM, so I don't think that's the bottleneck. And the insert isn't too complicated: "INSERT INTO data(session_id,game_id,count) VALUES (:a,:b,1) ON DUPLICATE KEY UPDATE count = count + 1". Are my MySQL settings screwed up somehow (I remember raising some buffer RAM value when I installed MySQL)? Select queries seem to run just fine, it's only inserts that are super slow.

[n00b590]

Wow never mind, http://dev.mysql.com/doc/refman/5.0/...ert-speed.html helped a lot. Start/commit transaction and combine all the inserts into one query FTW. Down to 30 secs now

[RoundTower]

Originally Posted by gaming_mouse

What's hard isn't writing a query to figure out what questions they havn't answered. What's hard is all the UX issues it raises:

Do you silently omit the question?
Do you display it but greyed out with the existing answer which cannot be changed?
Do you just show a message saying Question 6 has been skipped because you already answered it? Or do you just renumber the questions? Etc, etc.

You really have to think hard about your business goals and the user experience to answer questions like this. They might seem trivial and easy, but ime figuring stuff like this out is by far the hardest and most time consuming part of application development.

all this.

It seems no matter what you do you have to mix a little of business logic and database schema.