PostgreSQL 9.1 – Best Way to Create Tables for Email Campaigns

database-designpostgresqlpostgresql-9.1

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.

Best Answer

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.

Related Solutions

Sql-server – Table structure for many custom profiles

It always pains me to suggest it but theres a reasonable case for making use of an XML column here.

Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.

Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.

If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.

Database schema design for school system

To your specific question of (easily satisfied) plausibility, the answer is yes. Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be. A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term. A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels. You should decide where the department belongs and stop referencing it everywhere else. You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table. This leaves Classes and Courses. Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments). This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms

Best Answer

Related Solutions

Sql-server – Table structure for many custom profiles

Database schema design for school system

Related Question