create database structure?

Question

Post reply

create database structure?

ashuthinks

SSCommitted

Points: 1698
More actions
December 20, 2013 at 9:38 am

#301631

i want to create a database structure for following :
1 member can have many groups
1 group can have only one group member
1 group can be joined by many members
please help me to structure these in sql?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Sorry, but aren't these 2:

1 group can have only one group member

1 group can be joined by many members

mutually exclusive? Are 'members' and 'group members' different things? If so, what makes them different?

And then again, I might be wrong ...
David Webb

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

ashuthinks (12/20/2013)
i want to create a database structure for following :
1 member can have many groups
1 group can have only one group member
1 group can be joined by many members
please help me to structure these in sql?

I think what you mean to say is that you have a many to many relationship between members and groups. In other words, a member can belong to any number of groups and each group can have many members.

This is a very common situation. The way to deal with this is to use a third table that holds a foreign key to members AND a foreign key to groups.

So you would have 3 tables (Groups, Members and GroupMembers).

Here is some pseudocode to demonstrate.

create table Groups(GroupID primary key)

create table Members(MemberID primary key)

create table GroupMembers(GroupID, MemberID)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ashuthinks SSCommitted Points: 1698 More actions · Answer 3

thanks

i'm thinking to have ISOWNER column in 3rd table to set flag as tru/false.

IS IT GOOD APPROACH OR ANYTHING ELSE YOU SUGGEST TO ASSIGN A OWNER OF GROUP.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ashuthinks (12/20/2013)
thanks
i'm thinking to have ISOWNER column in 3rd table to set flag as tru/false.
IS IT GOOD APPROACH OR ANYTHING ELSE YOU SUGGEST TO ASSIGN A OWNER OF GROUP.

Can there be more than one owner of a given group? If the answer is no then absolutely do NOT put an owner column in the bridge table. In this case the owner is a property of the group and belongs in the group table. If however, there can be multiple owners then it would make sense in the bridge table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ashuthinks SSCommitted Points: 1698 More actions · Answer 5

one member can create many groups it means one owner have many group owned.

what if this is the case?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

ashuthinks (12/20/2013)
one member can create many groups it means one owner have many group owned.
what if this is the case?

The question "can one group have more than one owner?" determines where the IsOwner column should go. If a group can have more than one owner, then it belongs in the GroupMembers table. If a group can have one and only one owner, then it belongs in belongs in the Groups table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions