RE: Database design query
I think I have found the solution for my problem. I made the following changes: -> I added a new field RecordID in GroupMemberInfo to make the records unique -> Instead of MemberID and GroupID, I'm now using MemberName and GroupName. I made this change since in Active Directory every name is unique. -> What I found out that in mysql, a FK field can refer to any index field in parent table and not necessarily only Primary Key field. -> So instead of making MemberID and GroupID as primarykey, RecordID is primary key now and MemberID is just an index. I don't know if it's a bug in Mysql or it's an added feature that a FK field can refer to any index field in parent table. CREATE TABLE `groupinfo` ( `GroupID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberCount` int(11) default NULL, PRIMARY KEY (`GroupID`), UNIQUE KEY `i_GroupName` TYPE BTREE (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `groupmemberinfo` ( `RecordID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberName` varchar(128) NOT NULL default '', `MemberType` tinyint(4) NOT NULL default '0', PRIMARY KEY (`RecordID`), KEY `i_MemberName` TYPE HASH (`MemberName`), CONSTRAINT `FK_groupmemberinfo_GroupName` FOREIGN KEY (`GroupName`) REFERENCES `groupinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `hostinfo` ( `HostID` bigint(20) NOT NULL auto_increment, `HostName` varchar(128) NOT NULL default '', `Password` tinyblob NOT NULL default '', PRIMARY KEY (`HostID`), KEY `i_HostName` (`HostName`), CONSTRAINT `FK_hostinfo_HostName` FOREIGN KEY (`HostName`) REFERENCES `groupmemberinfo` (`MemberName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `userinfo` ( `UserID` bigint(20) NOT NULL default '0', `UserName` varchar(128) default NULL, `Password` tinyblob, PRIMARY KEY (`UserID`), KEY `i_UserName` (`UserName`), CONSTRAINT `FK_userinfo_UserName` FOREIGN KEY (`UserName`) REFERENCES `groupmemberinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I know for sure that this is not the best solution. But now both User and Host are referring to GroupMemberInfo. So I have constraints at DB level Thanks to all who replied, Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 01, 2005 8:39 AM To: 'rtroiana' Subject: RE: Database design query I know what you are trying to do and I can see the logic advantage of having a single table that defines the the group relationship for users hosts and groups. I just don't think the rules governing foreign keys will allow this. Your original thought of enforcing the relationships i.e. cascade delete/update etc. at the application vs the database is the only way I can see to make this happen. Otherwise you are back to three tables with the added code to find all members of a group across the 3 tables. I don't know that I've added much, but I enjoyed the dialog. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 4:23 PM To: 'Gordon' Subject: RE: Database design query I can have three different foreign key definitions on one field, but it expects the same value in all the three parent tables. So that's not the right way to implement it What I'm trying to do is: Member id as primary key and UserID, HostID and groupID as foreign keys But since in GroupMember table a member can be in more than 1 group, so I have combination of MemberID, GroupId and MemberType as primary key If I use these 3 as primary key, so UserID, HostID and groupID can't refer MemberID as primary key. That's what the problem is. ++Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 5:14 PM To: 'rtroiana'; mysql@lists.mysql.com Subject: RE: Database design query 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 betw
RE: Database design query
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]
Re: Database design query
Hi, i think you must normalize your table to more than one table. Users/Groups : N:1 Groups/Groups : N:1 Table Users : User_id Host Group_id Table Groups : Group_id Group_parent_id<- is a another group_id No data redondancy and robust implementation. see for example /etc/passwd and /etc/group on a *nix machine. Mathias Selon rtroiana <[EMAIL PROTECTED]>: > 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]
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
