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!

  • You still need to query which organizations are on the list to get their current email address, right? Jul 26, 2013 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? Jul 26, 2013 at 18:02

1 Answer 1


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


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)


-- add an org:

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

-- add an individual:

insert into individual (email_address) values ('[email protected]');

-- 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:

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.

  • 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? Jul 26, 2013 at 17:52
  • Do you want the number of emails sent or all of the addresses sent to? Jul 26, 2013 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. Jul 26, 2013 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? Jul 26, 2013 at 19:16
  • I would just add another table to track emails sent, and insert to that when the email blast was successful. Jul 26, 2013 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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