Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that I need to create to hold saved emails for FUTURE delivery (to, from, message, scheduled send date, etc).

The catch here is that I don't know who's supposed to receive the email until the day of delivery.

What I mean is, the email is created to go to certain selected organizations, but the "members" of the organization will be constantly joining and leaving, so if someone creates an email for delivery next month, the list of member email addresses to send to by then will be different.

So, what I need to save in the table is the list of organizations that the email should go to, so that I can query for the latest list of member email addresses when I actually send the email. Hope that makes sense.

Anyway, so my question is: what is considered a "proper design" for this?

My initial thought is to just save a comma delimited list of organization IDs.

I know I will never have to search on which organizations were on the list, so I don't care if it's not query-able, and I know I could normalize it into one row per recipient organization, but it seems such an unnecessary repeat of data for no purpose, especially since I only query on the SENDER not the recipients.

So is a list of IDs just a horrible, no good, only-a-newbie-would-think-of-that, bad thing? Or can it be used in some cases? Or is there some other way to do this that I don't know about? I'm sure I can't be the only one who's run into a situation like this before!

share|improve this question
    
You still need to query which organizations are on the list to get their current email address, right? –  Adel Khayata Jul 26 '13 at 3:20
    
Yes, in a way. Without digressing into long winded business logic, I can just say that if I try to get the email addresses in the same query I'd have to join about 8 tables because not just any member in the org gets the email either. I actually already have functions in my server code that gives me eligible emails of each organization, so I was just going to pass the list of org ids to this function. I realize that would be another call to the db, but only that once when sending the email. Otherwise, I don't display addresses in the future anyway. What do you think? Not the way to go? –  user2480201 Jul 26 '13 at 18:02

1 Answer 1

Do not use a list of ids. You will regret it. Use junction tables.

DDL:

create table organization (
  organization_id bigserial primary key,
  name text not null
);

create table individual (
  individual_id bigserial primary key,
  email_address varchar(255) not null
);

create table organization_member (
  organization_id bigint references organization(organization_id),
  individual_id bigint references individual(individual_id),

  primary key (organization_id, individual_id)
);

create table email_message (
  email_message_id bigserial primary key,
  send_at datetime not null,
  subject varchar(80),
  body text
);

create table email_message_organization (
  email_message_id bigint references email_message(email_message_id),
  organization_id bigint references organization(organization_id),

  primary key (email_message_id, organization_id)
);

DML:

-- add an org:

insert into organization (name) values ('Acme, Inc');

-- add an individual:

insert into individual (email_address) values ('user@example.com');

-- associate individual w org:

insert into organization_member values (1,1);

-- add an email message:

insert into email_message (send_at, subject) values ('2013-07-31 12:12:12', 'test');

-- associate an email message with an org:

insert into email_message_organization values (1, 1);

-- when it's time to send the email, get the right people:

select 
e.subject, 
e.body, 
i.email_address 
from individual i 
join organization_member om using (individual_id) 
join email_message_organization emo using (organization_id) 
join email_message e using (email_message_id);

-- send the emails, record that they were sent in another table

You should use the Party model for representing individuals and organizations, which is not what I used, but you should still look it up.

share|improve this answer
    
Ok, so here's my question. I only have to send the email once, but I have to load the sender's sent emails every time they log in. So in this case, each time, I'd have to pull a much larger set of data- lets say they send emails to 10 orgs each time and they have 100 historical emails. I'd have to retrieve 1000 rows and group by the sender (multiple senders in table) and group by email id in order to display their 100 emails. But I'd only have to retrieve 100 rows based on sender id if I used a list (I don't display recipient email addresses). Is the performance difference on that negligible? –  user2480201 Jul 26 '13 at 17:52
    
Do you want the number of emails sent or all of the addresses sent to? –  Neil McGuigan Jul 26 '13 at 18:47
    
you mean afterwards, like when the sender is looking at old emails? They will just see the message and date sent. It's not a real email system, more like a marketing blast. Once sent, it's sent, you can't receive, reply, forward or anything like that. So another reason to keep email addresses private and non-viewable from them. –  user2480201 Jul 26 '13 at 19:08
    
Actually, I was thinking more about what you said, and I realize that it would be beneficial to include the organization names that they sent to. Which, I guess means this is the best way to do it right? Better to pull 1000 rows at once joined to get org name, than to pull 100 rows but have to make a 2nd call to query org names from the list of org ids? Is that right? –  user2480201 Jul 26 '13 at 19:16
    
I would just add another table to track emails sent, and insert to that when the email blast was successful. –  Neil McGuigan Jul 26 '13 at 19:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.