Databases
StackOverflow2013
Postsdbo
POSQL 2005/2008 Database track field change
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL 2005/2008 Database track field change
    primarykey
    data
    text
    <p>I do <strong>not</strong> want Auditing or History tracking.</p> <p>I have an application that pulls its data from a external source. We mirror the external tables in our own DB. We allow our users to update the data in our DB. Next time the system syncs with the external data, we only override fields that we have not changed locally.</p> <p>Off the top of my head I can think of 2 ways to do this</p> <p><strong>1)</strong> Store 2 Rows for each Object. First 1 is the external version, the 2nd row links to the external version but will only have data in a field if that field has been changed. e.g.</p> <pre><code>id | parentId | field1 | field2 1 | null | foo | bar 2 | 1 | new foo | null </code></pre> <p>This illustrates what the data would look like when a local user changed field1. If no change occurred there would only be the first row.</p> <p><strong>2)</strong> Double the number of columns in the table. e.g name_external name_internal</p> <p>I like option 1 better as it seems like it would provides better separation and easier to query and to do in code comparisons between the 2 objects. The only downside is that it will result in more rows, but the DB will be pretty small.</p> <p>Is there any other patterns I could use? Or a reason I shouldn't go with option 1.</p> <p>I will be using .NET 4 WCF services </p> <hr> <p><strong>Solution</strong></p> <p>I will go with the two table answer provided below. and use the following SQL to return a Row that has the fields which have changed locally merged with the orginal values</p> <pre><code>SELECT a.[ID], isnull(b.[firstName], a.[firstName]), isnull(b.[lastName], a.[lastName]), isnull(b.[dob], a.[dob]), isnull(b.active, a.active) From tbl1 a left join tbl2 b on a.[ID] = b.[ID] </code></pre> <p>As in my the DB will only ever be able to be updated via the UI system. And I can ensure people are not allowed to enter NULL as a value instead I force them to enter a blank string. This will allow me to overcome the issue what happens if the user updates a value to NULL</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDaveo
    1. USDaveo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHow many fields? How many rows? What SQL version?
      singulars
      1. POSQL 2005/2008 Database track field change
      1. USHawxby
    2. CO20 tables, Between 2 and 20 columns on each table. Each table will have between 100 - 5000 rows
      singulars
      1. POSQL 2005/2008 Database track field change
      1. USDaveo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload