Server Load: mySQL vs PHP Functions

Question

Here is my situation:

Situation:

I have users, and I have multiple values in multiple categories for each user.

The way I see it I have two options:

I. Make a new table 'values':

 user_ID   category   value 

  ...        ...       ...
   5          1        aaa
   5          1        bbb
   5          2        ccc
   5          2        ddd
   5          2        eee
   6          1        xxx
   6          2        yyy
   6          1        zzz

II. In my actual table with the users - 'users':

user_ID (unique)         values  
        5             aaa,bbb$$ccc,ddd,eee
        6             xxx,zzz$$yyy

...where I save the values as text and parse it with ',' dividing values and '$$' dividing categories.

So far I have been doing some little things the second way, but now I expect a lot of values and categories for this database and I am wondering which will give me less server load -- having a very huge mySQL database table and go through it on every page to see which values belong to the current user ; or getting all and only his values from a relatively small table but parse the string every time with PHP to make it usable?

Answer 1

Having an unknown amount of data inserted into a field is a really bad idea since you don't know how to dimension it in the first place. Furthermore your DB is not going to be able to index it in any useful way and performance will degrade as the system grows.

Putting the categories and values in a separate table scales well and like adam already said, is what a relational DB is all about.

You might want to google "database normalization" and browse some results for more info.

Answer 2

The first method - that is what a relational database is built for. You also have the added benefit of indexes, which will keep it speedy up even with a fairly large dataset.

Also, it allows the data to be queried from the other direction - fetch me all users with category 2 equal to 'eee'