hi guys, pls i need advice and opinions on how to normalize a database table. The table would contain posts and comments 'likes'. Just like facebook likes, u can like a post and also the comments under it.

Meanwhile, the stuffs that can be liked are posts on a person's wall, the comments under it, a post on a business's page and the comments under it. Pls note that, a person can also remove his likes and can't like twice. If you also have differient suggestions, like breaking it into differient tables, pls do not hesitate to explain why.
Thanks

i still need this thing oooo

I think you were ambigous. U need several tables for this. A table shld rep an entity eg comments,likes,posts etc. Then join on ids. Use innodb and foreign keys if u can, so dat data remains fresh/avoid redundancy

webguru: I think you were ambigous. U need several tables for this. A table shld rep an entity eg comments,likes,posts etc. Then join on ids. Use innodb and foreign keys if u can, so dat data remains fresh/avoid redundancy

Why did u say, ambigous..

So u support, i should have tables named comments, likes, posts... Then on likes, have FK of comments id, and user id.

For you to enforce normalisation you must be dealing with at least two tables. Normalisation is used to prevent redundancy so in a case like this you can have the following 3 tables.

first table: members (member_id, member_name)
second table: comments (comment_id, poster_id, comment)
third table: likes (comment_id, member_id)

That structure above is normalised.

This structure below is not normalised
first table: members (member_id, member_name)
second table: comments (comment_id, member_id, comment, member_name)
third table: likes (comment_id, member_id, comment, member_name)

The fields in bold are not necessary and will cause an update anomaly when you update the member's name in the member table. This will cause redundancy of information because once that member_name is updated in the members table, the member_name fields in the other tables will not be updated except you intend to update all in the code layer which wont be sensible.

ehhmm, i think you guys are getting me wrong. I know about database normalisations and its benefits. What am asking in this instance is that;

I have 4 kind of stuffs to be liked:
A post on a persons profile
A comment under the post

A post on a business page
A comment under the post

All the above have there tables seperatly already. Now if i create just 1 table for likes, imagine the data and size of the table. Therefore, am asking if i should have 1 table for all the Likes or 4 tables for the differient like-able objects. Then, if you have any other suggestion apart from mine, i would love to hear them. Thanks

My suggestion: have two tables like so:

like_types (like_type, like_id);

likes (like_id, foreign_key, member_id)


For now, the like_types table will contain four rows with an auto_incremented ID (like_id). In the future you may have other things you may want people to like, for instance you may want people to start liking pictures or videos. You simply add that to the like_types table.

The 'foreign_key' in 'likes' table is the primary key of any of the for types of likes from the four tables you have for them.

Dual Core: My suggestion: have two tables like so:

like_types (like_type, like_id);

likes (like_id, foreign_key, member_id)


For now, the like_types table will contain four rows with an auto_incremented ID (like_id). In the future you may have other things you may want people to like, for instance you may want people to start liking pictures or videos. You simply add that to the like_types table.

The 'foreign_key' in 'likes' table is the primary key of any of the for types of likes from the four tables you have for them.

hmm. on point.
at times you think you are the only one in the world who reasons like this.