A lot depends upon the sophistication of the program you write to manage this. I doubt there is any way to create something so sophisticated with just SQL.
My first thought would be to use three tables. Make sure every user has a unique use ID. The users' passwords would be stored in the table of users (I'm assuming that the passwords are unique to users, rather than groups.) user_id autoincrement user_name user_pass <User other information> Each group would also have a unique group id. The table of groups would only contain three fields: group_id autoincrement owner's user id group_name This lets you find each user's owned groups. The name field is so that a user can readily see which group is which in a human-readable way. Then you want a table of group members, again with only two fields: group_id member_id Now to find a user's groups, you look for the user_id in the group table. To find its members, you look in the group member table. You can also work backwards to find all of the groups that a user belongs to by starting from the other direction. The password checking for managing a user's groups would be at the application level. You'd have one record Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: ppywriw [mailto:[EMAIL PROTECTED] > Sent: Saturday, December 16, 2006 11:54 AM > To: mysql@lists.mysql.com > Subject: database design help > > > Hiya, > > Real easy quick question. > I need to design a database which holds users with email, > name and some > other details. > I also want each user to be able to create one or more groups > of users, > owned by themselves. > What would be the best design approach? > > So far i have a table for the users which stores their > personal details, but > i dont know where to go from here to create the groups? > Create a new table > for every group? The group would just contain a list of the > users emails in > that group. > Or would i create a new table for the groups and attach a > password field on > it so only the user that created it could access it? > > A very newbie question i know, but i am one, i'll admit it. > > Any help would be apprectiated. > > Thanks > > John > -- > View this message in context: > http://www.nabble.com/database-design-help-tf2832533.html#a7908028 > Sent from the MySQL - General mailing list archive at Nabble.com. > > > -- > 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]
