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:
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:
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.
Best Answer
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.