Best practice database design - user rating tables I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this:

IID | UID | Rating 
i.e. 

item ID |user id |rating.


From collaborative algorithm examples the tables rather look like:

Item ID | Rating U1 | Rating U2 | ... | Rating Un


This means i would have to alter (extend) the table every time a new user signs up. Alternatively i have to create a new table for each user. Both does not seem to be very efficient solution. Can anyone point me into the right direction?

Thanks
The first design is normalized, the second design is in 0NF (un-normalized).  You haven't stated any reason to denormalize and you should never denormalize without a good reason.

Here is why the second design is very bad:


Think about what your queries to calculate an average rating would look like in each case.  
Think about what you're going to do when your number of users exceeds your DBMS' limit on the number of columns per table.
You stated in a comment that you are concerned about duplication of data because of foreign key values, however, how much space are you wasting by having a sparsely populated table?  If you have 1,000 users and only 10 of them have rated each item on average, then you're wasting 990 bits of null flag per item.  
Constantly changing your table width as users enroll can be a big performance hit because of having to move chunks of data around when the new column doesn't fit in the available free space.


You asked for best practice.  Best practice for transactional data is third normal form (or higher, if applicable).