0

I am developing a proof of concept for a personalized email sending web application (like MailChimp, but with premade dynamic templates). The general structure is that I have my templates (different email templates that the user can choose from), the email object that the user is creating (from/to/subject), and his list of recipients with different fields (firstname/lastname/email address/etc). The web application will be multi-tenant so we need to have a flexible structure. My problem is the following:

Depending on the template the user chooses, the recipients will need more or less properties (home address/insurance type/promo code/gender/etc). One template can require only the basic recipient information and another one could require 50 different attributes. I will have a table Templates, a table PersonalizedFields with the TemplateID in it, so I can know which fields are required for my recipient datasource.

I am hesitating for the structure of the recipient object/table. At first I thought about E-A-V, but after reading about it, I am not so sure it is the best structure. I will be using Entity framework to interact with the database and one list of recipients can have 50k record with 0 to 50 (fringe cases) additional properties. The structure would be like this:

E-A-V

Another structure I am considering is putting the additional properties in a XML column, which will be convenient because the email composition tool I am planning on using takes XML but I will still need to process it:

XML fields

Or maybe a third structure I am not aware of?

The original data will be read from an XLS(X)/CSV file and then written to the database, and later on read from the database to be formatted in XML strings.

What would be the best data structure?

The technology used for the proof of concept and the future project will be C#, ASP.NET MVC for the web application, ASP.NET MVC API for the service that will do the heavy lifting of the processing, and SQL Server 2008 for the database. Entity Framework will be used to interact with it.

  • What did you read about eav that makes you feel it is inferior to XML? They both have strengths and weaknesses, make sure you read both sides of the argument - sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/… – Aaron Bertrand Aug 11 '15 at 14:28
  • Thank you for the blog post, it is a similar problem that I am currently facing. My understanding of why EAV might not be the optimal solution stems from this post. I am not against it, but I am wondering if it is the best solution for the project, and if there is limitation with EF. I will need to read from a XLS/CSV file most of the time and write to the database, and later on read from the database and convert it to a XML file. – Fynnen Aug 11 '15 at 14:35
  • IMHO, sacrificing your data model to suit the limitations of EF and reliance on bloated XML is like buying a Yugo because it has a nicer cupholder. But hey, if you really need to use EF and XML, then I'm not sure why you're asking for an opinion, you may as well just store it as XML and deal with all the problems that brings. – Aaron Bertrand Aug 11 '15 at 14:58
  • 1
    I agree with @AaronBertrand on this one. I have had great success with Dapper personally. It might be worthwhile to see if it suits your needs. There are so many tools out there that it is almost never a good idea to make huge sacrifices for your tool IMHO. – Erik Aug 11 '15 at 15:47
0

It looks like you want to fill out the email templates using only the unique data for each recipient. If this is true then I would recommend option 3. I would have a recipient table that is not directly linked to the email, but is instead linked to the email via a join table. I would also put a FK on the recipient table to the email sender. Then I would basically follow your EAV model.

This way your "recipient" table is more like a contact table for the sender. The EAV model allows you to easily select the custom data for the templates, gives you the ability to painlessly extend the data points, and gives you the opportunity to allow your users to create their own templates using the fields they want. The senders also win because they can reuse the 50+ data points for future emails because we decoupled the recipient table from the email.

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.