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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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.