Multiple insertion of datas into the Db

Topics: General Discussion Forum, July and December Releases Forum
Apr 3, 2008 at 9:13 AM

I am facing a problem with the Multiple insertion of datas into the Db.
My aim is to Register new users, with their userGroups.
So One user can be a member of more than one Group.
Fileds of Users table are :


In a list box i am providing all the Groups, so that the Adminstrator can select the UserGroups more than once.

I am storign this Selected Usergroups in a Strign array and sending it to the Service.
In the BL first of all i am inserting the User Entry(like ,username,fullname, phoneNumber ) into the DB.
After that i am taking the Max of UserID.

Now here comes the difficult task where i am stuck.

here i have multi[ple userGroups, for the multiple user Groups i have to take the GroupID with respect to the UserGroup.
The Fields of the UserGroupUsage Table is:


So for one User i have to enter More than one Group into the Db...

I hope any one of you can help me,

Thanks a lot in Advance,
Apr 3, 2008 at 2:23 PM

I may get better respones for these questions on db forum and this newgroup may not be best place. But I will try to reply to your question based on what I understand:

1. Prefer using IDENTITY column. Returning MAX will not work in multi-thread applications
2. Create a composite key on UserGroup table KEYUserID, UserGroupID - where UserID is from User table and UserGroupID which can be called 'GroupID' -is from Group table.

Also, prefer collection than string with comma separted values: when you pass list of group IDs for user OR when you return them from db.

You can always use ID column with IDENTITY type -on tables where you can not decide on what should be its PRIMERY key OR to simplify accessing records on table with too big/complex composite key.