Solved

EmaiilGroups_TableDesigns

Posted on 2012-03-27
6
315 Views
Last Modified: 2012-04-16
Does anyone havea good DB design for creating and soring email group lists.

Basically I have a contacts table with a structure like

contactid
first_name
last_nameemail_address
phone
cellphone
etc.

I have several applications and i need to alert several users after different processes execute.

For example, the production group may want to send john@aol.com and mike@aol.com after Production job runs.

QA group may want to send johN@aol.com and alice@aol.com after QA process runs.

etc,. etc.

so I am thinking of creating another table for storing Email groups with a list of the people that should receive the emails.
The list can be updated (i.e remove people and add new people).
0
Comment
Question by:sam15
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37774733
I assume the question here is if there are other options?

What you describe is like a disto list and is what I would probably do.  This way, changes can be done with a table insert or delete not an app change.

I might also suggest the core table you describe can be deemed not normalized.  What if I have two email addresses or phone numbers?

Might want to add a contact-types table with the unique types then a intermediate table, person-contacts table to resolve the many to many relationships.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37775009
I agree with slightwv that you may have this issue of many-many relationships...

I had got this similar situation long back, instead of mails we use trigger sms after the activity completion..

will post the description of my table may be it will work for you too...



Module   varchar2(100),    --- name of the module on which alert has to be sent, group all contacts by their modules(in your case it is activity)
Owner     varchar2(100),    --- to all the concerned persons to whom alert(email here) to go
threshold  varchar2(100), --- you can ignore this, its needed incase of escalations(not applicable here for you)
alert_flag   varchar2(1) default 'Y', -- this is to be used for whether alert is to be triggered or not, using this you can avoid deletion of records if no alert has to be sent.
mobile_no  number, -- contact number of person to whom alert has to be sent
email_id varchar2(200) -- email id of contact to whom alert has to be triggered
last_update_date date, -- last date of when the alert has been triggered
remarks      varchar2(200)  --- can be used for your own tracking purposes aftr program completion...
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37775819
assuming that contactid is primary key for all the contacts, i would rather have table like this to store groups for one job
groupid int (Primary Key)
jobid int (Foriegn key from some other table that contains job information)
groupName varchar(100)(Just a friendly name for group)
groupDetail xml(contacts that are part of this group represented in xml)

xml can look like this:-
<group>
<Contact>
<Id>1</Id>
<Name>John</Name>
<Email>john.j@test.com</Email>
</Contact>

<Contact>
<Id>2</Id>
<Name>mark</Name>
<Email>mark.a@test.com</Email>
</Contact>
</group>

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sam15
ID: 37776137
Yes, A person might have multiple emails.

I thought there would be some standard schema design for something like this. I was trying to pick up some ideas. I have seen some databases where they store the email distribution lists as a long string separated by commas like (joe@aol.com,mike@yahoo.com,susan@msn.com).
I think though this is not good normalized solution and difficult to maintain.

But it seems to me the ERD would be like

USERS can have ONE OR MANY email addresses.
EMAIL GROUPS/LISTS can have ONE OR MANY USERS.

It seems to me this requires 4 tables:
a) CONTACTS to store user info,
b) CONTACTS_EMAILS to store email addresses,
c) EMAIL_GROUPS to store email distribution lists
d) EMAIL_LOG to store the email information to be sent via job.

Any ideas, or links with sample schemas for this.
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37776148
why don't you like to go for xml option....
any specific reasons for that....
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37776476
>>I think though this is not good normalized solution and difficult to maintain.

Agreed.

>>But it seems to me the ERD would be like

I'm thinking (off the top of my head...might need tweaked a little):
contacts: contact_id, name (or first and last)
       1, Larry
       2, Moe
       3, Curly
contact_methods: method_id, type
       1, email
       2, phone
       3, carrier pigeon

contact_contact:  cc_id, contact_id, method_id, value, is_primary
       1,1,1,'larry@stooges.com','Y'
       2,1,2,'1112223333','Y'
       3,2,1,'moe@stooges.com','Y'

email_groups:  group_id,group_name
       1,'Stooges'

email_group_members: member_id, group_id, cc_id
       1,1,1
       2,1,3
     

       

>>why don't you like to go for xml option

XML can be hard to maintain.  To do a simple phone number change involves opening/updating the entire XML doc likely with the DOM.

Imagine removing a 'type' all together:  Since Carrier Pigeons are extinct:  Just delete a few rows.  If stored in XML, deleted nodes from every record.  Very messy.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to send multiple emails at the same time in PHP 12 58
Oracle SQL - Query help 7 54
make null the repeated levels 2 20
Wordpress plugins not working in html 3 23
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question