• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to store Multi valued attributes in any database?

 
Manas Saxena
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am building a social networking website with facility for user to join groups.
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.
 
T Mishra
Ranch Hand
Posts: 108
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I do not think JSP forum is a correct place to discuss database model / design.
Are you using a Structured Database ? If yes, you might need to check Many To Many relationship.
 
Manas Saxena
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry i am new around here how do i know where my post has been moved to?
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64618
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As you are posting in it, I guess you found it.
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's a fairly thorough discussion of how to implement many-to-many relationships in this thread.
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.
 
Manas Saxena
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster Thanks my doubt is solved now
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic