Effective-Dated Data Tables and Related Data
Effective dates are extremely important for all business systems and I really think this option should be baked into the data entities. All of the demo projects are great, but they're not useful in real-world scenarios where data items such as "course title" change over time.
Example: Given the following two tables --
CompanyTable (CompanyID, EffectiveDate, CompanyName)
OrderTable (OrderID, EffectiveDate, CompanyID)
A CompanyName can change over time. When a record from the OrderTable is shown on a screen, the CompanyName should reflect the the name of the company at the time of the order (based on the effective date of the order).
You might say, "Oh, that's easy because each effective-dated row will have a unique ID," but you can't do that because the Company dropdown on the order would show multiple rows for each company.
Right now, it's going to take a lot of custom work (WCF RIA) and duplication of data. I think this needs to be a basic property at the data entity level. In order to serve as a robust business development platform, LightSwitch should be aware of effective dates and related data items.
shannonwhitley
shared this idea
7 comments
-
Simon Jones
commented
Yes, I can see that in HR you might need to add historic records after the fact and so might need *History tables more than in general business documents such as Companies and Orders.
-
The CompanyTable and OrderTables are just examples. My primary work is in Human Resources and we need to make corrections to historical records on a regular basis. The system would be very inefficient and prone to errors if we had to correct every related record in the database for each correction.
For the sake of the discussion, I've included this article which includes additional information on effective dated tables.
http://www.sqlservercentral.com/articles/Effective+Dating/67806/As Simon has pointed out, there are some approaches that can be taken to introduce simple effective dating. If LightSwitch will be used to support complex enterprise systems, and I hope it will, then effective dating should be introduced into its core functionality. Otherwise, everyone will be left trying to force it to do something that it wasn't designed to do.
-
Simon Jones
commented
You need to add a TOP(1) clause and an ORDER BY clause for efficiency.
SELECT TOP(1) *
FROM CompanyHistory
WHERE CompanyID = @CompanyID
AND EffectiveDate <= @EffectiveDate
ORDER BY EffectiveDate DESCThat will return you the first row on or before the effective date for this company.
Arranging a JOIN to the Orders table means using a derived table I think.
This is not an easy thing to do and while it is correct (third normal form) it is a complexity that you can legitimately trade for a little duplication of data to make life a lot easier.As for "correcting history", I don't think you should. Users should check that orders are going to the right company and change the company name/address if necessary before creating the order. You shouldn't go back and edit an order to change its details because it should be a record of what actually happened. If a mistake has been made the Order should be marked as withdrawn (not deleted) and a replacement created.
-
Yes, I'm currently using the WHERE clause as you suggest, but that's not very efficient. It's going to pull all of the records prior to that date and then just display the latest record. And that works okay for a form, but I believe you'll run into an issue if you try to display that data in a grid. If you do that for multiple tables on the same page (which I am doing now), the system will start to drag.
If you copy the data down, anytime a user makes a historical correction, you'll have to handle the data syncs for all of the historical rows.
In my opinion, these are really all hacks that should be included in the base logic of the entities.
-
Simon Jones
commented
Company and CompanyHistory should be two separate tables.
You have to retrieve the correct dated records yourself if you use a CompanyHistory table using a WHERE clause. WHERE CompanyID = @CompanyID AND EffectiveDate <= @EffectiveDate
Copying the data down to the Orders table does duplicate data but it should not give any inconsistencies and disk space is cheap so should not cause any concerns. It also gives you more "self-contained" documents that are easier to report on and for power users to understand for reporting. -
Hi Simon,
The company table in my example is essentially a CompanyHistory table. LightSwitch still doesn't know how to display related data for a particular date. And copying data is a very bad idea, you'll end up with data inconsistencies and bloated databases. I work with ERP systems like PeopleSoft, and this is a very common requirement.
-
Simon Jones
commented
You can do this already, either by using a CompanyHistory table or copying the CompanyName (and Address data) down to the Orders table when the Order is created.
