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.