0

Survey (MySQL):

  1. The answer table stores answers to survey questions.

  2. The user_profile table stores answers to user profile questions.

  3. The user table only stores primary key.

I need user table to get relation between answer and auser_profile.

Is it correct to have user table that only stores primary key for that relation?

enter image description here

Олег Нечаев
  • 59
  • 6
  • 2
    This is a one-to-many relationship (one answer can have only one user, but one user can have multiple answers) so there is no need for a link table. Just store the user ID in the `answer` table. – BenM Aug 05 '19 at 15:18
  • The `user` table you refer to sounds like a linking table. A more "proper" name for it would be `answer_userProfile_relations`. The user_profile table is simply a `user` table. – Robert Harvey Aug 05 '19 at 15:19
  • 1
    As BenM points out, unless you're modelling a many-to-many relationship, you don't need a linking table. – Robert Harvey Aug 05 '19 at 15:20
  • i assume the same question can be answered by multiple users/persons? That is more general in a survey system... – Raymond Nijland Aug 05 '19 at 15:21
  • 1
    Your schema look sound, so let me just say this: I can't think of any good reason why you might want a table that contains solely a primary key. – Robert Harvey Aug 05 '19 at 15:22
  • 1
    *"I can't think of any good reason why you might want a table that contains solely a primary key."* i have a very good one @RobertHarvey no primary key in InnoDB means it will table lock because of thread mutex because of the way [dict_sys_get_new_row_id](https://stackoverflow.com/a/57233092/2548147) is implemented.. – Raymond Nijland Aug 05 '19 at 15:24
  • Well, I'm not sure I'm understanding the question anyway. – Robert Harvey Aug 05 '19 at 15:25
  • @RobertHarvey I try to avoid using 'emai', 'tel' and etc. fields inside 'user' table. I want move this fields into user_profile table and leave only primary key. It's because I need 20 more questions to fill out user profile. – Олег Нечаев Aug 05 '19 at 15:25
  • OK. So what's the problem? – Robert Harvey Aug 05 '19 at 15:27
  • 2
    For what it's worth, questions about "correctness" are mostly unanswerable. You have to state specific criteria for correctness in order for such questions to be answerable in any meaningful way. Most non-trivial program code can't even be proven to be correct, so I'm not sure why people insist on "correctness" in even more ambiguous realms than code. – Robert Harvey Aug 05 '19 at 15:29
  • @RobertHarvey the profile is stored as fields (`name`, `email`, `tel`). I need to keep the profile in the same form as it present in the answer table. So I have created `user_profile` table (see the image below). Now I have user table that only stores primary key for relation. How I can get relation between `answer` and `user_profile` without user table? I notice that I don't need to store any fields except primary key. If I want to store others it will be ok to use Many to one relation between user - answer and user - user_profile. – Олег Нечаев Aug 05 '19 at 16:03
  • @BenM what i want looks exactly like this https://stackoverflow.com/a/951817/7088378 – Олег Нечаев Aug 05 '19 at 17:40

0 Answers0