Suggestion for better relational DB Schema

Question

Hi,

I am here to design a relational database schema that models polling and users. Each category can have one or many questions. Each user can participate only once at each category, and can poll (or not) exactly once on each question. Each poll is yes, no, or abstain (no vote).

I have designed my schema with four tables:

 users, [userId, IP]
 category, [catId, catTitle]
 question, [queId, queTitle]
 polls [pollId, queId, userId, answer]

is better or

 users, [userId, IP]
 category, [catId, catTitle]
 question, [queId, queTitle]
 polls [pollId, catId, userId]
 pollAnswers [pollAndId, queId, pollId, answer]

I would like to know which one is better and why?

as per me since i dun have any extra information about polls i am directly cross joining users and questions with answer.

I also do need to find how many users were abstain for a.) all category question b.) particular category c.) particular question

I have my view as for choice one schema :

select U1.*, Q1.*, P2.*, C1.*
from 
    ( users U1, 
    questions Q1 )
    Left outer Join polls P2 on 
        Q1.queId = P2.queId AND U1.userId = P2.userId
    Left Outer Join category C1 on
        Q1.catId = C1.catId

I am trying to worried using above query with cross join between users and question will loose my performance or not?

If second schema is better can u suggest options for my results?

Answer 1

Take a look at a similar question, database design for a survey.