数据库结构允许用户对某个主题进行投票

I have a Job model and a User model. I need each User to be able to answer several questions based on a particular Job. Each question is marked from 1 to 10 (or unanswered).

I believe i need a hasMany through associations, but have been confused by these before.

Can someone help me with my database/model layout. Thanks.

EDIT:

• The only tables I have at the moment are users and jobs
• The questions are the same for every job, there are six of them (maybe more in the future)
• The Questions will always be numbers from 1 to 10 (or unanswered)

Yes, perfect candidat for hasMany through, and I'll do it like so:

User hasMay JobQuestion
Job hasMany JobQuestion

JobQuestion belongsTo Job, User

put all your questions in the JobQuestion model. There might be a more appropriate name for this model. Do you need more details for the models keys?

Edit:

A row in the JobQuestion model would:

id | user_id | job_id | question1 | ... | questionN

Your data array would something like

Array
(
    [0] => Array
        (
            [JobQuestion] => Array
                (
                    [id] => 1
                    [user_id] => 1
                    [job_id] => 1
                    [question1] => answer1
                    [question2] => answer2
                )

            [Job] => Array
                (
                    [id] => 1
                    [name] => somejob
                )

            [User] => Array
                (
                    [id] => 1
                    [username] => someuser
                )

        )

)

I think it should be:

Tables: User, Job, Question, Answer

• Job hasMany Questions
• User hasMany Answers
• Question hasMany Answers

Your Answer table could have the following columns: user_id, question_id, answer(NULL or 1-10)

You don't need a Question table if it's integrated into Job somehow.

EDIT: Updated to remove redundant id from Answer table as per comment suggestion.

If I am understanding your model properly, it sounds like users select jobs, and then answer questions specific to that job (some of which may apply to more than one job)? if so I'm thinking your table structure should look something like this:

First there are your discrete entities.

Users:
    UserID (PK)
    User

Jobs:
    JobID (PK)
    JobName

Note that if some of the questions are general in nature and apply to more than one Job, then Questions should be defined as follows:

Questions
    QuestionID (PK)
    Question

Otherwise, if each question is specific to a particular Job, your COULD avoid at least one association table and incorporate an FK to Jobs into the questions table:

Questions
    QuestionID
    JobID
    Question

But I don't recommend this unless you are certain that Questions are inique to each job.

Next, your associations:

User_Jobs (Composite key = FK on USerID, FK on JobID)
    UserID 
    JobID

Job_Questions (Composite Key = FK on JobID, FK on QuestionID)
    JobID
    QuestionID

User_Job_Questions (this one would contain a feild for responses, and could also be named User_Response)
    UserID
    JobID
    QuestionID
    Response (likley constrained to ints from 1-10)

Note that User_Job_Questions would contain a composite FK on JobID and QuestionID relating back to Job_Questions and another FK back to Users.

Job hasAndBelongsToMany Question as the same question can apply to multiple jobs.

However to implement your answers you use this approach, a HasManyThoughJoin . In The JobQuestion table you could store the answer AND the user_id.