What is the best way to create tables for email campaigns?

Question

I have table email_sent with fields:

• id
• user_id
• template_name
• sent_at
• opened clicks

And now I need to save data which was send for each email. I have several campaigns with differnt data. For example, campaign wich recommend call to english teacher, if user call to teacher until expire date service will return 10% of charge. So for each email I need to save teacher_id and expire date. I have serveral ways.

First to create table call_discount with fields: email_sent_id, teacher_id, expire.

Second to create table email_sent_data with fields: id, fieldname, value and table email_sent_to_data with fields: email_sent_data_id, email_sent_data. But I will have huge amount of data and it will be hard to make right indexies.

Third to create table email_sent_data with fields: id, teacher_id, expire. Add column email_sent_data_id to table email_sent. And if I have new campaing I will add new fields to email_sent_data.

I have postgresql 9.1. So I can't work with JSON.

Answer 1

True, Postgres 9.1 does not have json / jsonb data types. But there is the additional module hstore providing the data type of the same name.

hstore has been in the core release since Postgres 8.3 (it's very mature). For just text data and no nesting it's just as good as json, if not better. (jsonb introduced some new candy.)

If you only have a few dozen of sparsely populated columns I would also consider wide rows with many columns just being NULL. NULL storage is very cheap in Postgres.

• Many columns vs few tables - performance wise

You could also go with an EAV (Entity-attribute-value) design. Related answer discussing pitfalls:

• Is there a name for this database structure?

But consider upgrading to a current version of Postgres first. Postgres 9.1 reaches EOL in Sept 2016.