User data. More tables or 1 table with more coumns?

I need to store data about users like Hobbies, Looking for, Countires I visited, Favourite movies, ...

I am wondering if it is better to create for each data a new table or 1 table for all. For example would better to put hobbies into table Hobbies (userid | hobbie) and than for each hobbie a new row or better to have 1 table for all which is Hobbies, Looking for and other user data and put for example all user hobbies into 1 field. Than I would use PHP explode() function to get all hobbies.

Where is the limit when to choose another table. Everything what is more than 1 data (for example 2 different hobbies) in field? Does is take for performance more if I need to put multiple mysql_queries and selecting 1 column or 1 mysql_query but selecting more columns?

Thank you

 

if your table's columns would be something like this:

id, hobby1, hobby2, hobby3 ...

it would be better to use 2 tables

table1: id,...
table2: user_id, hobby

 

no, my table would be like
usedrid | age |hobbies | adddress | ...
--------------------
12 | 22 |basketball;music;tennis | ...
52 | 32 |dancing;volleyball | ...

instead of two tables:
usedrid | hobbies
--------------------
12 | basketball
12 | music
12 | tennis
52 | dancing
32 | volleyball

usedrid | age | address
--------------------
12 |22 | ...
52 |32 | ...

Which one do you think is better?

 

Two tables

 

to answer such questions always think in terms of usecases.

if you go single column you are assuming that
1) everytime i am looking for information about a person i am showing all of the values in the table.
2) people mostly have different hobbies etc.

if u r going multiple tables ie one table for each data u r assuming that
1) user might want to know about only a few types of details
2) most of the users have similar choices. (as in you provided a drop down to choose from)

in my opinion go for multiple table scheme. most ppl r accustomed with it.

 

hello,

Its all depends upon your requirement. How you want to use this data & in future will somebody need to change this data.... All upon requirments....

but the right way is to create two tables to maintain consistent data....

tables will look something like this:

UserTable
ID         hobbiesID
1             11, 12, 13
2             12, 13
3             11

hobbiesTable
hobbiesID        hobbies
11                    watching movies
12                    playing games
13                    dancing

Code (markup):

In this way data will be consistent.....
Now question is why....

beocz when you will make any change in hobbiesTable such as you want to change

hobbiesID 11 value from "watching movies" to "watching TV", then if will effect your whole of the database.....

if you are creating tables like this

UserTable
ID         hobbiesID
1             watching movies, playing games, dancing
2             playing games, dancing
3             watching movies

hobbiesTable
hobbiesID        hobbies
11                    watching movies
12                    playing games
13                    dancing

Code (markup):

Then, if you change hobbiesID:11 to watching TV in hobbiesTable then your data will not be consistent. You will have some data in user table which does not exist in your hobbiesTable.

If you have any question, then ask I will try to explain.

Thanks,

Jimmy