An efficiency and design question

[Xel]

Hello,

 

I'm working on a site that allows users to register and solve certain tasks. I want to make it so that each user's profile page displays his/her solved (and partially solved) tasks. Also, each user will see his/her solved tasks in a certain color in the tasks list. I thought about two possible ways of implementing this:

 

1. Create a table for each user that stores their score for each task. This would be the easiest solution, since all I'd have to do at each step is do a SELECT * FROM <current_user table>. Is this efficient or advisable however? Why or why not?

 

2. Only keep a "Users" table that stores all users, with fields such as username/password/email/etc and a field called "Tasks" which could keep the information about the tasks in a CSV format like so: 1 100,2 20, 3 30, 4 0, 5 0 which would translate as "100 points for task 1, 20 pts for task 2, 30 pts for task 3, 0 for task 4, 0 for task 5". In my opinion this is no more efficient than the table version, because I'd have to declare plenty of space for this field. It would also be harder for me to parse the information, after doing a search for the current user. It would also be harder to update the information. Intuitively, this seems less efficient, but someone suggested I take this approach.

 

I don't know how many users and tasks I can expect. In time, however, they could both go into the thousands.

[J.Daniels]

A field should only contain 1 value.

 

I would create the Users table to store the user information.  Then I would create a linking Tasks table that would include the userID for one field, the Task number for the next, and finally the value for that task in the last field.  It would look something like this:

 

User {UID, username, password, ... }

Tasks {UID, Task, Points}

 

[Xel]

Let me see if I got this straight. Considering I have the users X(UID 1), Y(UID 2) and Z(UID 3) and the tasks 1 2 3. I would have this users table:

 

1, X, md5hash

2, Y, md5hash

3, Z, md5hash

 

And the Tasks table could look like this considering each user had attempted each task:

 

1 1 10

1 2 100

1 3 25

2 1 10

...

 

Wouldn't each user show up as many times as the number of tasks he attempted? Wouldn't it be inefficient?

 

 

Also, you said a field should only contain 1 value. I meant I could make a big varchar field (declare it 10000 or something) and store values in it in that format. I don't know how efficient this would be, but it would work.

 

I'm very new at this, so sorry if I'm missing something. What is your opinion about creating a table for each user, each table containing a list of tasks, score records for that user?

 

EDIT: I've read about linking tables. I'll have first of all the users table with uname, email etc. and also a tasks table with task ids, names, difficulty etc. Could I maybe make a Scores table that links with these two? How would it work? Wouldn't redundancy still be an issue?

[J.Daniels]

When I mentioned that a field should contain one value, I meant that it should represent one value.  You are correct that you can concatenate several values into one string and parse it out later, but the theory behind a relational database is that one row equates to one record or transaction.

 

I'm not sure what the difference in efficiency of the two methods would be (one linking table or one table for each user), however, the "best practice" would be to use the linking table.  This way, you do not need to create a new table for each new user, and the linking table can be keyed.  If each user were to perform the task only once, you can set the primary key to (UID, TID) as they would be unique in the table.  If each user were able to perform a task more than once, you want an auto incrementing primary key, then set the UID and TID as foreign keys.  I believe this should help the database engine index the records.

 

So, if you had the User and Task tables:

 

User {userID, username, password, ... }

Tasks {taskID, name, difficulty, ... }

 

The linking Score table if a user can perform a task once:

 

Score {userID, taskID, Points}

 

or if they can perform a task multiple times:

 

Score {scoreID, userID, taskID, Points}

 

 

This way there is no redundancy as each record is unique.  Hopefully, I have explained this well enough.

[Xel]

Yes, that is clearer. The users can attempt a task multiple times, but only their latest attempt will be taken into consideration. This means I should make (UID, TID) as the primary key, right? I'm not exactly clear what this means - can you have two primary keys?

 

Also, how would inserts / updates be done? When a user submits something for a task, I would check the scores table to see if there is already a record for that user and task. If yes, I'd update it with the new score and if not I'd insert one, correct?

[J.Daniels]

Yes, that is clearer. The users can attempt a task multiple times, but only their latest attempt will be taken into consideration. This means I should make (UID, TID) as the primary key, right? I'm not exactly clear what this means - can you have two primary keys?

 

Yes.  A primary can consist of one or more columns.

 

Also, how would inserts / updates be done? When a user submits something for a task, I would check the scores table to see if there is already a record for that user and task. If yes, I'd update it with the new score and if not I'd insert one, correct?

 

If you are using MySQL, there is the REPLACE function that works like an INSERT, but if the PRIMARY KEY(S) exist, it will DELETE then INSERT the new record.

[Xel]

Thank you, I understand now.

 

I think I will do it like this, although I still don't see why having a table for each user is frowned upon, other than aesthetics / elegancy.

[corbin]

...I still don't see why having a table for each user is frowned upon, other than aesthetics / elegancy.

 

 

Read a few things on database normalization.

[fenway]

The stickies are your friend