Need help designing table with list of IDs to store

database-design

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!

Best Answer

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.

Related Question