Thanks Pierre and Taher for your responses, a couple of follow-ups below:
On 8/9/16 01:44, Pierre Smits wrote:
The one thing is related to creating a unique key based on the fromDate in
combination with the other elements of the primary key. As an example, have
a look at the following (excerpt) of the entity ProductCategoryMember :
...
As you can see from above examples, the thruDate is not required to
determine record uniqueness. It only determines applicability in time.
I'm under the belief that no entity can have more than one active
version - e.g., the from/thru dates must not overlap. Is that correct?
I was thinking that your current entities would always have a null/magic
number/infinity thruDate so using thruDate instead in the index would be
more efficient for queries but clearly you could put a thruDate on your
active entity so that doesn't hold up. Thanks for clearing that up.
Does selecting the active version of an entity essentially always
includes a where clause checking that the current date is between the
from/thru date? E.g.:
SELECT * FROM productCategory WHERE productCategoryId = 100 AND now()
BETWEEN fromDate AND thruDate
Do these entity tables have separate indices on fromDate/thruDate for
query performance?
Brian
