aspose file tools*
The moose likes JDBC and the fly likes How to store Multi valued  attributes in any database? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to store Multi valued  attributes in any database?" Watch "How to store Multi valued  attributes in any database?" New topic
Author

How to store Multi valued attributes in any database?

Manas Saxena
Ranch Hand

Joined: Mar 01, 2013
Posts: 38
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

Joined: Apr 04, 2006
Posts: 108

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.


Thanks,
Tushar (SCJP 1.5)
Manas Saxena
Ranch Hand

Joined: Mar 01, 2013
Posts: 38
Sorry i am new around here how do i know where my post has been moved to?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61202
    
  66

As you are posting in it, I guess you found it.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1705
    
  14

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.


No more Blub for me, thank you, Vicar.
Manas Saxena
Ranch Hand

Joined: Mar 01, 2013
Posts: 38
chris webster Thanks my doubt is solved now
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to store Multi valued attributes in any database?