title: Entity Framework - Saving a snapshot of a document in a SQL database
body: <p><strong>Background</strong> I am using EF6 and is free to change the database.</p>
  
  <p>I ran into this problem today. Say I have:</p>
  
  <pre><code>public class Company
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }
  
  public class Address
  {
      public int Id { get; set; }
      public int CompanyId { get; set; }
      public string Line1 { get; set; }
      public string Line2 { get; set; }
  }
  
  public class Invoice
  {
      public int Id { get; set; }
      public int CompanyId { get; set; }
      public int AddressId { get; set; }
      public bool IsCompleted { get; set; }
  }
  </code></pre>
  
  <p>The users are allowed to update <code>Company</code> and <code>Address</code>. Users are also allowed to update <code>Invoice</code>. But since it is a financial document, it must somehow save a snapshot of the address if the user marks <code>IsCompleted</code> to true.</p>
  
  <p>Currently it is done in the following way:</p>
  
  <pre><code>public class Invoice
  {
      public int Id { get; set; }
      public int CompanyId { get; set; }
      public int AddressId { get; set; }
      public bool IsCompleted { get; set; }
  
      //Auditing fields 
      public string CompanyName { get; set; }
      public string AddressLine1 { get; set; }
      public string AddressLine2 { get; set; }
  }
  </code></pre>
  
  <p>I think this is hard to follow. I was thinking:</p>
  
  <p>Option 1: Save an audit to an audit table of its own:</p>
  
  <pre><code>public class Invoice
  {
      public int Id { get; set; }
      public int CompanyId { get; set; }
      public int AddressId { get; set; }
      public bool IsCompleted { get; set; }
  
      //Null if IsCompleted = false. 
      public DateTime? CompletedTimeStamp { get; set; }
  }
  
  public class CompanyAudit
  {
      public int CompanyId { get; set; }
      public string Name { get; set; }
      public DateTime TimeStamp { get; set; }
  }
  
  public class AddressAudit
  {
      public int AddressId { get; set; }
      public string Line1 { get; set; }
      public string Line2 { get; set; }
      public DateTime TimeStamp { get; set; }
  }
  </code></pre>
  
  <p>But then that seems like a lot of tables to create and a lot of work if we do change the schema for Company and Address. Also, it's not very robust. I can't reuse this for other documents without a bunch of wiring. However, this is what I found mostly on the internet. One audit table for each table.</p>
  
  <p>Option 2: Save all the audit to the same table:</p>
  
  <pre><code>public class Audit
  {
      public int DocumentId { get; set; }
      public string DocumentType { get; set; }
      public string JsonData { get; set; }
      public DateTime TimeStamp { get; set; }
  }
  </code></pre>
  
  <p>But then, this seems like it's not very standard. I have never saved Json data to a SQL database before. Is this bad? If so What could go wrong?</p>
  
  <p>Should I go with Option 1 or Option 2?</p>