Date effective tables allow us to create a new version whenever the data is changed, and see the date at any point in time. This sounds great, and will no doubt find requests to make all tables date effective. There is a penalty. The first is that it brings a little complexity to the process of developing the tables, reports, and user interface. The other is that it creates a new record every time, and can affect performance. We should only use this if we really need it. Great examples include the human resource tables to allow history of name changes, previous and planned positions, addresses, and so on.
In our example, we will create a new table for values such as odometer readings for a vehicle. We will create a table for this that is date effective so that we only record a new version when these key fields are changed.
