0

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.

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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