Database concept

Elmobram22

Registered User.
Local time
Today, 23:42
Joined
Jul 12, 2013
Messages
165
Hi all,

I want to make a database of contacts with email addresses and telephone numbers. And I want to assign them to groups but I want them to be possible to be assigned to multiple groups. Basically so I can write an email and send it to the group I want to but then get access to send only one email if they are in multiple groups I choose. I'm wondering the best way of setting the tables up. I'm thinking along the lines of TblContacts, TblGroups and TblAssign. In the TblAssign I would have a fk for the IDs of both contacts and groups but I don't know whether this is an overbloated way of doing it. Any suggestions would be greatly appreciated.

Cheers,

Paul
 
I think that is the best way. The way I would avoid duplicate emails is to have a Sent Boolean field in the TblContacts table. As you iterate through tblAssign to allocate the addresses, only include the contact if the Sent field = FALSE (and set the Sent field to TRUE at that point). At the end of the send routine you would reset Sent field = FALSE.
 
Don't think it's necessary to add a boolean field and flag it / unflag it as you go? Requires looping...

Why not just query the tblContacts with an inner join on the tblGroups (i.e. select all e-mail addresses from tblContacts which link to the specified groups) and use the DISTINCT keyword to only return each unique address once?

Á la :

Code:
SELECT DISTINCT [tblContacts].[EMailAddress]
FROM [tblContacts]
INNER JOIN [tblGroups] ON [tblContacts].[SomeCriteria] = [tblGroups].[SomeCriteria]
 
Paul,
It seems that each Contact has only 1 email address-- is that true?
Your design (M:M) allows 1 Contact to be in many Groups. Easy to Add/Modify/Delete
records in each table.

tblContact
ContactID PK
ContactFirstName
ContactLastName
ContactEmail
ContactPhone
other ContactInfo

tblGroup
GroupID pk
GroupName
GroupDesc


tblAssigned
--a junction table
ContactID fk to tblContact
GroupID fk to tblGroup

Query to select distinct emails for mass emailing by Group
Code:
select distinct contactEmail 
from 
tblContact
,tblGroup
,tblAssigned
where
tblGroup.GroupId = tblAssigned.GroupID and
tblContact.ContactID =tblAssigned.ContactID and
tblGroup.GroupName = [Enter the GroupName for this mass emailing]
 
Last edited:
Wow thanks for all the answers guys. I ain't made a start on this yet but your thorough info is definitely going to help when I do. I'll let you know how I get on.
 

Users who are viewing this thread

Back
Top Bottom