How to handle many to many relationship (1 Viewer)

[danb]

Hi, here's the scenario...

I'm building an ASP based email application which is to send emails to different user groups. The email addresses in the database need to be able to be assigned to more than one user-group, and an administrator needs to be able to add user-groups to the database.

So effectively, there may be hundreds of user groups and hundreds of users.

What is the best way to store this info in an Access database? Do I let the administrator create a new table column in the userEmails table whenever there is a new user-group added, or do I store a series of userGroupIds in a text field along with each email address and delimit them so that I can split them into an array?

I'm really not sure how to go about this, so any help would be much appreciated.

Thanks...

 

[snoko]

You will basically need 3 tables

tblUser
-------
intID - Autonumber (PK)
strName
etc
etc

tblGroup
--------
intID - Autonumber (PK)
strName
etc
etc

tblUserGroup
------------
intID - Autonumber (PK)
intUserID - Long Integer (FK)
intGroupID - Long Integer (FK)


Hope this helps

 

[Pat Hartman]

For the usergroup table, I would suggest a unique index on the userID and GroupID to enforce the business rule that a user can belong to a group only once.
tblUserGroup
------------
UserGroupID - Autonumber (PK)
UserID - Long Integer (FK)(Unique idx fld1)
GroupID - Long Integer (FK)(Unique idx fld2)

The alternative to the unique index is to eliminate the surrogate key on this table and use the two fields as a multi-field primary key. If this were my database and I did not have any child tables to tblUserGroup, I would use the multi-field PK rather than a surrogate key.