shoud we normalize user profile table with fields like interests,musics,videos,sports, etc?

Question

I've read in an article about social networking database schema and saw that they've used varchar for those columns that I mentioned in the question title. It's not normalized! Isn't it better to have a table for sports and then put the foreign keys in user's profile table? In this case we'll have much less redundancy? Please correct me if I'm wrong. Which approach is better have another tables for fav_sports,fav_videos,fav_music, etc or put them all in user's table?

Answer 1

The solution I would choose depends on a few things. If the number of these columns is relatively low (and not subject of frequent changes) then I would go with the solution you suggested, ie. one table for each attribute and foreign keys on these. This way you have to define a new table and add a column for every new attribute and modify your queries accordingly.

If this is not your case than EAV suggested by @Zachariha should be considered, but it's worth reading this answer before. It is very easy to extend (just add a new row to a table) but can go very difficult to maintain - in my opinion mostly because it's hard to follow that who plays in which team.

The scenario you see in your current schema can be the result of either not normalizing data or denormalizing later in order to speed up queries. Normally, I keep heavily denormalized tables separate from base tables to keep my data safe from violations of referential integrity.

Answer 2

I do not see how to add a comment so I'm answering but part is a comment to the first answer.

The schema @Zachariha Robichaud is suggesting, under my view, is to have on the Extra table the different interests genres, maybe football, basketball, heavy metal, pop, sci fi. Then on the Type table, sport, music, books. And finally the Map_User_Extra will poing out the triplets you can have combining all the 3 tables. It will help to have a many-to-many relationshop.

I our case, eComm application, we normalize everything, I'm not 100% agree but that's another story. In your case I would use just one more table, as you commented on your question. But as there will be few sports or any other interest, according to your comment, I would use just one table for all of them instead of one for each. This table would have an ID and name of the interest. On the user table I would add a column with reference to the particular interest.

Answer 3

you could have a reference table that references the users.user_id to a extra.extra_id table.. and inside of the reference table you can add the type or type reference..

soo like the schema could be

users
-----
user_id  int PK
f_name varhchar(32)
l_name varchar(32)
phone varchar(11)

extra
--------
extra_val int PK
field varchar(32)
value varchar(255)

type
-------
type_id int PK
name varchar(32)

map_user_extra
---------------
map_id int PK
user_id int
extra_id int
type_id int

The query would be something like

SELECT * 
FROM map_user_extra AS map LEFT JOIN
     (user, extra, type) ON
     (map.user_id = users.user_id AND
      map.extra_id = extra_id AND
      map.type_id = types.type_id)

Is this what you are asking?

---

Sorry for my tardiness with my reply.

Butt, yes you would combine the tables with a many to many relationship. You could have many "types" for many "users". where type would be sport types. And the "extra" table could be for extra columns that are un-thought of.. like maybe running times or weight of the runner.. its a rough schema.. and deff is not perfect but might have been useful as a guide.

again im sorry for the late reply and lack of a better description. butt, i hoped to help.