Fork me on GitHub
#off-topic
<
2015-08-19
>
sveri14:08:48

Hi, anyone here has knowledge about database design? I have two tables user and question and now I need a many to many relation ship between them, which is the easy part (user_question table). Now comes the tricky part. question has a one-to-many relationship to answers which is ok too. However, now I have another information, the answers from a question that one user has answered with an additional attribute (true / false). How would I model that? My idea is to have another many to many relation between user and answer and put a third field into that table ("correct" or something). does that make sense? At least that's how I did it up to today and I wonder if there is a common solution to that problem?

swizzard14:08:48

between user and answer, or between question and answer?

swizzard14:08:19

but yeah, i’d add an additional column to the many-to-many table

sveri14:08:24

between user and answer

sveri14:08:43

but it doesn't matter I guess, the problem is the same

glv14:08:13

You didn’t say this explicitly, but it sounds like all of the questions are T/F questions. True?

sveri14:08:37

glv: yea, that's one of their property

glv14:08:52

Do you want to limit yourself in the future to only being able to support T/F questions?

sveri14:08:35

as this is for multiple choice tests I think I am certain for maybe 99% that it stays like this

glv14:08:14

Wait … multiple choice is different from T/F.

sveri14:08:06

well, you have several questions, each containing several possible answers and each answer being either true or false

sdegutis14:08:20

So, how bout dem bears?

sveri14:08:36

No beer now, thank you 😄

glv14:08:41

Oh … so it’s not really true and false, it’s “checked/not-checked” or something like that.

glv14:08:13

The value of the checked field (if that’s how we choose to model it) might be boolean, but the meaning is whether they checked that choice or not.

sveri14:08:40

glv: I agree, the semantics are a bit different

sdegutis14:08:48

So what do you guys think of music?

sdegutis14:08:01

Personally I'm a fan.

sveri14:08:18

of thinking or of music?

glv15:08:08

@sveri: just off the top of my head, I might have a table called “responses” with three fields: user_id, question_id, answer_id. The presence of a row in the table indicates that the user checked that answer for the question.

sveri15:08:34

@glv: Sounds good, why didn't I think of this myself? simple_smile

glv15:08:38

Also, a terminology issue: someone looking at this and seeing “answers” might think that means “what the user answered”. Consider names like “possible answers”, “choices”, “answer options”, or something like that.

sveri15:08:59

true, I am hesitant to use long table names as it blows up the code pretty fast

sdegutis16:08:15

I think I may be in the wrong room. I thought this was for off topic.

sdegutis16:08:19

Sorry yall.

dottedmag19:08:52

@magnars: what does the little "x" mean in your shell at the beginning of PotD E1? after mkdir -p

juhoteperi19:08:40

@clumsyjedi: Vim-cider has now find-symbol functionality. I used the opportunity to learn more about VimL's great functional programming facilities.

juhoteperi19:08:05

Also I added resolve-missing last night.

magnars20:08:02

@dottedmag: the x in my shell means there are uncommitted changes in the git repo.