0

I'm trying to think about how to structure my database schema in order to store merge fields but running into issues pertaining to different types of values (e.g., unsigned int, string, date) depending on the field name. This is what I have so far: 

Customer
----------
PK - id 
First Name - String
Last Name - String
Email - String 


MergeField
--------------
PK - id
Name - String

CustomerMergeField 
-------------------
PK - id,
Customer.ID - id, 
MergeField.ID - id
Value - String, unsigned int, date

So for instance there is customer (1, bobby, fischer, bobbyfischer@gmail.com) and these two merge fields (1, birthday) and (2, school)

So in the CustomerMergeField table might have [1, 1, 12-15-2012], [1, 2, 'Columbia'],

I'm wondering how can I better structure this to handle the difference in values types i.e. unsigned int, string, date, etc that can be associated with a mergefield so that it is more uniform

Patricia Rozario
  • 151
  • 1
  • 2
  • 10
  • May I ask why you think you even need the junction table `CustomerMergeField` here? Why not just associate the birthdate and school directly with each customer? – Tim Biegeleisen Apr 18 '18 at 01:15
  • Because the merge fields are used as part of email templates so the merge fields associated with each customer would be variable to the emails being sent for a specific campaign – Patricia Rozario Apr 18 '18 at 01:18
  • 1
    Storing dates and text in the same column isn't going to work well, most of the time. – Tim Biegeleisen Apr 18 '18 at 01:19
  • What you describe is cslled the entity-attribute-value design. See Bill Karwin's answer on EAV design and its alternatives in the duplicate topic. – Shadow Apr 18 '18 at 01:27

0 Answers0