I am having a table named users which contains a column named group which must contain all the groups that the corresponding user is in.
One approach could be to store group ids as varchar separated by commas.However i read somewhere that it is not generally accepted practise also making a new table entirely for groups seems to just increase redundancy.
Any answers and suggestions are welcome.
I am building a social networking website with facility for user to join groups.
So you need something to represent Users, something to represent Groups, and something to represent the group(s) a user belongs to. We could call this User-Groups.
I am having a table named users ...
OK so far.
...which contains a column named group...
Uh oh, starting to go wrong here.
... which must contain all the groups that the corresponding user is in.
Definitely off-track here.
One approach could be to store group ids as varchar separated by commas However i read somewhere that it is not generally accepted practise....
Definitely not the way to go!
...also making a new table entirely for groups seems to just increase redundancy.
Don't worry about "redundancy" here: creating a new table for each entity is exactly what you need to do here. Think about what you are representing i.e. Users, Groups and User-Groups. Think about how to uniquely identify each User and what information needs to be stored for each User. Do the same for Groups. You will probably decide (wisely) that each table needs a unique numeric ID column as its primary key, so your Users table will have a User ID, and your Groups table will have a Group ID.
If a User could only belong to one Group, then you would have a simple parent-child relationship i.e. one Group can contain many Users, but one User can only belong to one Group. This would be implemented via a foreign key between Users and Groups.
But you want to allow each User to belong to many Groups, and each Group can contain many Users. So you have a many-to-many relationship, and the standard way of representing this in a relational database is via an intersection table e.g. User-Groups, which provides a way to map between Users (identified by User IDs) and Groups (identified by Group IDs).
I reckon that's enough information for you to figure out the implementation yourself.