Looking for ideas or recommendation on a data structure design

[josh]

Background for Q: What I am building is form that will allow a user to
select email addresses (filtered by department) from a listbox and add
them to some sort of list (not sure how the list will be implemented
yet). These email addresses will be used for an automated email
notification system when certain events occur.

Users will be able to add themselves and others for these
notifications. There will be a number of notifications that they can
sign up for as well. There will hundreds and maybe even thousands of
these notifications over the course of a 6 month period.

I'm trying to architect a solution for holding these addresses for each
notification. One option is a normalized structure but that seems
over-built. Another option is to store the selected addresses in a
memo field, but that seems nasty/messy and could cause the database to
get huge.

Q: Anyone have a better idea of how to accomplish the task at hand?

Thanks for your time,

Josh Blair
Evergreen, CO

 

[Allen Browne]

You have a classic many-to-many here.
When a particular type of event occurs, you want to email several people.
Each person can also be associated with many types of event.
The solution is a junction table that resolved the many-to-many into a pair
of one-to-many relationships:

Person table (one record per person). Fields:
PersonID
Surname
Firstname
EmailAddress

EventType (one record for each type of event. Fields:
EventTypeID
EventDesciption

EventEmail (one record for every person associated with an event type):
EventTypeID
PersonID

Now when an event of a certain type occurs, you have all the persons who
need to be associated, so you can get their email addresses from the Person
table.

It may be that things are more complex than this structure allows. (For
example, if Jo Smith has 2 email addresses, and you must use one for one
type of event, and another for a different type, you will have an Email
table related many to one to the Person table, and then the EventMail table
will have a foreign key to this particular email address instead of directly
to the person.)

 

[joshblair]

Allen,

Thanks for the tip. I'll give it a go. I also found some very useful
info on you site.

Thanks again,

Josh Blair
Evergreen, CO