Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Shon Levi
Shon Levi
6,036 Points
Posted by Shon Levi
Shon Levi
6,036 Points

sql structure

Hey you all, I'm trying to build some sql database for some practices but can't figure out how to manage it.

I want to make a list of questions where each row has: id of question, the question, the answer.

For each user I need to run some counter of how many time he succeed to answer the right question (for example: John - question number 02 - 4 times answer right).

How can I manage such a thing?? I trying to think how it can be managed but all I came with is just adding each user column for each question id - but what if I have 1000 questions? my users table should store 1000 columns? one for each question? it seems very bad idea.

Hope to get some direction or solution, Thanks for help!

4 Answers

jcorum
jcorum
71,830 Points

Well, here's one way to do it. You need a third table, commonly called a join table, to relate the Users and Questions tables.

Users table:

--
-- Table structure for table `Users`
--

CREATE TABLE `Users` (
  `userID` int(11) NOT NULL,
  `firstName` varchar(30) NOT NULL,
  `lastName` varchar(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Users`
--

INSERT INTO `Users` (`userID`, `firstName`, `lastName`) VALUES
(1, 'Tom', 'Brown'),
(2, 'Nancy', 'Drew'),
(3, 'Harry', 'Potter'),
(4, 'Tom', 'Swift');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Users`
--
ALTER TABLE `Users`
  ADD PRIMARY KEY (`userID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Users`
--
ALTER TABLE `Users`
  MODIFY `userID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Questions table:

--
-- Table structure for table `Questions`
--

CREATE TABLE `Questions` (
  `questionID` int(11) NOT NULL,
  `question` text NOT NULL,
  `answer` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Questions`
--

INSERT INTO `Questions` (`questionID`, `question`, `answer`) VALUES
(1, 'Number of states in India?', '29'),
(2, 'Number of countries in Africa?', '52'),
(3, 'Height of Mt. Whitney (in feet)?', '14,505 feet'),
(4, 'Depth of Mariana trench (in feet)?', '36,070 feet');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Questions`
--
ALTER TABLE `Questions`
  ADD PRIMARY KEY (`questionID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Questions`
--
ALTER TABLE `Questions`
  MODIFY `questionID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

UserAnswers table (the join table):

CREATE TABLE `UserAnswers` (
  `userAnswersID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `questionID` int(11) NOT NULL,
  `answeredCorrectly` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `UserAnswers`
--

INSERT INTO `UserAnswers` (`userAnswersID`, `userID`, `questionID`, `answeredCorrectly`) VALUES
(1, 1, 1, 4),
(2, 2, 3, 2),
(3, 1, 4, 0),
(4, 3, 1, 3),
(5, 3, 4, 0),
(6, 4, 2, 5),
(7, 4, 1, 1);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `UserAnswers`
--
ALTER TABLE `UserAnswers`
  ADD PRIMARY KEY (`userAnswersID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `UserAnswers`
--
ALTER TABLE `UserAnswers`
  MODIFY `userAnswersID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;

SQL query to join the three tables and show the users, the questions and the number of times they answered correctly:

SELECT firstName, lastName, question, ua.answeredCorrectly 
FROM Users AS u 
INNER JOIN UserAnswers AS ua ON ua.userID = u.userID  
INNER JOIN Questions AS q ON q.questionID = ua.questionID 
ORDER BY firstName, lastName, q.questionID

Output (though you will see it doesn't line up in columns the way it would in the DBMS due to the way I have to copy it here):

rstName Ascending 1     lastName Ascending 2    question    answeredCorrectly   
Harry   Potter  Number of states in India?  3
Harry   Potter  Depth of Mariana trench (in feet)?  0
Nancy   Drew    Height of Mt. Whitney (in feet)?    2
Tom     Brown   Number of states in India?  4
Tom     Brown   Depth of Mariana trench (in feet)?  0
Tom     Swift   Number of states in India?  1
Tom     Swift   Number of countries in Africa?  5

I'm making a bunch of assumptions here re what you really want, that you don't want incorrect answers as well as correct ones, etc. Also, the table schemas are minimal -- but I'm sure you know how to add additional fields, and if you want them in the output you can modify the sql query.

Hope this helps!

Shon Levi
Shon Levi
6,036 Points
Shon Levi
Shon Levi
6,036 Points

Wow! You are so great!!

Can you please just explain me why we need userAnswerID and why it need to be PRIMARY KEY?

And what is "ENGINE=InnoDB"?

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

I'm not sure the model itself makes sense - why would user answer the same question over and over?

But disregarding that for a moment, you might implement this with 3 tables. You already described the questions table.

Then for users table you might have userid, username, and any other personal info for the user.

Finally, a results table (also known as a junction table) where each row has userid, questionid, timeright, (maybe also timeswrong). The primary key would be both userid and questionid, and both can be foreign key references to the other tables. Then there would be one row added for each question a user answers, and then that row would be updated if the user answers the same question more times.

Besides saving you from having a large number of columns in any table, with this scheme you would not have to modify the table structure if you add new questions.

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

Re UserAnswersID - some DB folk would say you don't need a PK for a join table. I've found that it's a good idea to always have a PK for every table. There's a bit of debate on this topic, as you might imagine. And various arguments pro and con. And I won't go into it all here. There are, as you might imagine, many articles on the web.

But leaving theory aside, there's a very practical reason: without a PK my DBMS won't allow CRUD actions from the Browse window. So if you are using MySQL and phpMyAdmin and want to make quick changes to data in a join table like this you need the PK. Of course, you could make those kinds of changes using SQL statements, but sometimes, especially setting up a demo like this, it's very handy just to be able to make them in the Browse window.

Shon Levi
Shon Levi
6,036 Points
Shon Levi
Shon Levi
6,036 Points

hey jcorum,

just started to write and run my code, and I notice that I have to have row for each word * users...

every user that register my app - I need to enter rows for all question with correct answer = 0

There is some way to pass it? it will become very large database when many users will register... (assume I have 1000 questions... it 1000 rows for each user...) Think you have some idea to make it more efficient?

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

Shon, if you have a 1000 users and each answers, say, 100 different questions, then you will have 100,000 rows in the UserAnswers table. Actually, that's not a lot of rows. I've created databases behind websites for schools with 1200 students or more and there are join tables between students and classes and grades for each of 8 classes per semester per year, etc. They ran fine. That's what RDMSs excel at.

There are several issues re efficiency, which usually means performance. One is that you do need to add an index to the UserAnswers table on the two foreign key fields: userID and questionID. The index must have both fields (not one index per field. That will make a significant difference re performance. I can't believe I sent it to you without doing that. I guess I was in a bit of a hurry.

The second issue is that any design without a join table would be much more inefficient!

The third issue is that when a table just has three or four int values per row it would have to be very, very large before you started to notice any problems. Much larger than what you are expecting.

Hope this answers your questions.