IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table.
Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -----Original Message----- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember (GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement these relations through Db constraints. I wanted HostID and UserID should refer to MemberID as Primary Key. My problems is a foreign key field cannot refer to a part of primary key, so 1) I should add GroupID in Host and User table, which will be redundancy of data, or 2) Instead of adding a new field, I should not have any relations in the database and just implement it in code. I have tried with three different designs, but all of them have some issues. I tied to add a new table just for Member that would store unique memberID. Does it seem like an overhead? I don't if I can just do with existing table or not. I read some articles online, some of them say it's good to implement relations from DB and some say to reduce overhead, relations can be implemented from code. What would the best database practice that you would suggest? I'll appreciate any help Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
