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?
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