1

I’ve been working on a Windows Form App using vb.net that retrieves information from a SQL database. One of the forms, frmContract, queries several tables, such as Addresses, and displays them in various controls, such as Labels and DataGridViews. Every year, the customer’s file is either renewed or expired, and I’m just now realizing that a change committed to any record today will affect the information displayed for the customer in the past. For example, if we update a customer’s mailing address today, this new address will show up in all previous customer profiles. What is the smartest way to avoid this problem without creating separate rows in each table with the same information? Or to put it another way, how can versions of a customer’s profile be preserved?

Another example would be a table that stores customer’s vehicles. 

VehicleID | Year | Make | Model  | VIN                | Body
---------------------------------------------------------------
1         | 2005 | Ford | F150   | 11111111111111111  | Pickup
2         | 2001 | Niss | Sentra | 22222222222222222  | Sedan
3         | 2004 | Intl | 4700   | 33333333333333333  | Car Carrier

If today vehicle 1 is changed from a standard pickup to a flatbed, then if I load the customer contract from 2016 it will also show as flatbed even though back then it was a pickup truck.

I have a table for storing individual clients.

ClientID | First    | Last      | DOB
---------|----------|-----------|------------
1        | John     | Doe       | 01/01/1980
2        | Mickey   | Mouse     | 11/18/1928
3        | Eric     | Forman    | 03/05/1960

I have another table to store yearly contracts.

ContractID | ContractNo | EffectiveDate | ExpirationDate    | ClientID (foreign key)
-----------|------------|---------------|-------------------|-----------
1          | 13579      | 06/15/2013    | 06/15/2014        | 1
2          | 13579      | 06/15/2014    | 06/15/2015        | 1
3          | 24680      | 10/05/2016    | 10/05/2017        | 3

Notice that the contract number can remain the same across different periods. In addition, because the same vehicle can be related to multiple contracts, I use a bridge table to relate individual vehicles to different contracts.

Id | VehicleID | ContractID   <-- both foreign keys
---|-----------|------------
1  | 1         | 1
2  | 3         | 1
3  | 1         | 2
4  | 3         | 2
5  | 2         | 3
6  | 2         | 2

When frmContract is loaded, it queries the database and displays information about that particular contract year. However, if Vehicle 1 is changed from pickup to flatbed right now, then all the previous contract years will also show it as a flatbed.

I hope this illustrates my predicament. Any guidance will be appreaciated.

Missing Semicolon
  • 11
  • 4
  • Usually with an History table and triggers on the main table. When you want to retrieve the historical info you go to the history table for that Vehicle. [Look at this QA](https://stackoverflow.com/questions/11890868/how-to-store-historical-records-in-a-history-table-in-sql-server) or read [Using Triggers to Track Database Action History](http://www.4guysfromrolla.com/webtech/091901-1.shtml) – Steve Dec 08 '17 at 22:45
  • Thanks @Steve, these links are really helpful! – Missing Semicolon Dec 09 '17 at 20:00

1 Answers1

1

Some DB systems have built-in temporal features so you can keep audit history of rows. Check to see if your DB has built-in support for this.

ravioli
  • 3,749
  • 3
  • 14
  • 28