RE: Table design suggestions?
This wouldn't work for a member belonging to several groups. 3 tables is the way to go. Cordialement, Damien COLA -Original Message- i'd set it up like this Table Group id int auto_increment name char Table Member userID int auto_increment name char groupID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
> Table Group > id int auto_increment > name char > > Table Member > groupID int auto_increment > name char > > Table Relationship > group_id int > member_id int > i'd set it up like this Table Group id int auto_increment name char Table Member userID int auto_increment name char groupID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
given this, how would you write a query to list all members that are part of group_id=1 AND group_id=2 AND ... group_id 20. without having to write 20 "JOIN relationship r1 on r1.group_id =1 JOIN relationship r20 on r20.group_id =20) wich gets extremely slow with large ammounts of JOINS On Wednesday 11 June 2003 08:47 pm, JJ wrote: > I have to add group functionality to an existing database. The > functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wed, 11 Jun 2003 22:17:09 -0230, "JJ" <[EMAIL PROTECTED]> wrote: [...] | *** | * The second idea is to create two tables. | *** | table groups | groupName varchar primary key | groupDescription text | | table groupRelations | groupName | groupMember | | -> ex: | groups table: | groupOneFirst test group | groupTwo Second test group | | groupRelations: | groupOnemember1 | groupOnemember3 | groupTwomember2 | groupTwomember3 | | Like I said before, I'm not very happy with either method. If anyone else | has any better ideas they wouldn't mind sharing, I'd really appreciate it! This scheme will give you the most flexibility. There is no limit as to how many people can be in a group or how many groups a person can be in. You will want to create a joined unique key on the group_id and member_id. Have Fun! Michael -- Michael Brunson 504.200. [EMAIL PROTECTED]ICQ: 83163789 Data Center Manager -- www.zipa.com --Zipa, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wednesday 11 June 2003 08:47 pm, JJ wrote: > I have to add group functionality to an existing database. The > functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design suggestions?
I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. I have a need to create and maintain a list of groups. Then, each group will have a list of members. I will need to be able to search by member name and get a list of what groups the member is in. Also, I will need to maintain the members in each group (add, delete members). I would appreciate any suggestions on how best to model this in MySQL. I've come up with two ideas and I'm not crazy about either. *** * The first idea is simple to create a table with two columns: *** groupNamevarchar, primary key groupMembers text (comma seperated list) -> ex: groupOnemember1, member2, member3 groupTwo member2, member4 *** * The second idea is to create two tables. *** table groups groupName varchar primary key groupDescription text table groupRelations groupName groupMember -> ex: groups table: groupOneFirst test group groupTwo Second test group groupRelations: groupOnemember1 groupOnemember3 groupTwomember2 groupTwomember3 Like I said before, I'm not very happy with either method. If anyone else has any better ideas they wouldn't mind sharing, I'd really appreciate it! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
