Theory of Composite keys with From date vs Thru date?

[Brian Ghidinelli]

I'm a big fan of Silverston and his UDM books and have been studying OfBiz extensively. I was wondering if anyone can explain why his entities with from/thru date use the from date instead of the thru date in the composite keys? It seems like you would want to enforce only one version is ever active at a time by uniquely indexing the thru date as null (or a magic end date, or infinity in Postgres).

I'd be very appreciative of any theory explanations on the use of from date instead of thru date?

Brian

[taher]

Hi Brian,

As per my understanding some entities have only one relevant date and most
of these dates represent the issue date (from_date). Also for relationship
entities the date that matters is the fromDate because what you care about
is the linking date.

Usually the way I search through the data is by finding the max fromDate
where thruDate is null. I think these two conditions might be sufficient
for your needs unless I missed something else in your mail. And finally I
think some entities are not holding the best designs and there is always
room for improving so getting more concrete examples might enrich the
discussions.

HTH

Taher Alkhateeb

On Aug 9, 2016 12:04 AM, "Brian Ghidinelli" <[hidden email]> wrote:

>
> I'm a big fan of Silverston and his UDM books and have been studying OfBiz
> extensively. I was wondering if anyone can explain why his entities with
> from/thru date use the from date instead of the thru date in the composite
> keys? It seems like you would want to enforce only one version is ever
> active at a time by uniquely indexing the thru date as null (or a magic end
> date, or infinity in Postgres).
>
> I'd be very appreciative of any theory explanations on the use of from
> date instead of thru date?
>
> Brian
>

[Pierre Smits]

Hi Brian,

Welcome to the OFBiz community.

It seems to me that you are mixing two different things, but I might be
misunderstanding you.

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 :

        <prim-key field="productCategoryId"/>

        <prim-key field="productId"/>

        <prim-key field="fromDate"/>


When looking at following data definitions, you'll see that nothing more is
needed to create a unique primary key:

   1. <ProductCategoryMember productCategoryId="100" productId="GZ-1000"
   fromDate="2001-05-13 12:00:00.0"/>
   2. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2001-05-13 12:00:00.0"/>
   3. <ProductCategoryMember productCategoryId="101" productId="GZ-1001" f
   romDate="2001-05-13 12:00:00.0"/>
   4. <ProductCategoryMember productCategoryId="101" productId="GZ-1001" f
   romDate="2002-05-13 12:00:00.0"/>
   5. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2002-05-13 12:00:00.0"/>

Item 1 and 2 are different because the productIds are different.
Item 2 and 3 are different because the productCategoryIds are different
Item 3 and 4 are different because the fromDates are different

Now, items 2 and 5 are a bit odd, as both productCategoryIds and productIds
are the same, but the fromDates are different. One could say that there is
not enough information to determine uniqueness. Thus here comes the
thruDate field into play. So let us extend those two data definitions.


   1. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2001-05-13 12:00:00.0"  thruDate="2005-05-13 12:00:00.0" />
   2. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2001-05-13 12:00:00.0"  thruDate="2006-05-13 12:00:00.0" />
   3. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2006-05-13 12:00:00.0" thruDate="2011-05-13 12:00:00.0" />

But does that make any sense?
In the case of items 1 and 2 the end date (thruDate) is different, but
basically the same information set. It would be sufficient to just have 1
record with having the correct end date, as the start date doesn't change.

   1. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2001-05-13 12:00:00.0"  thruDate="2006-05-13 12:00:00.0" />
   2. <ProductCategoryMember productCategoryId="100" productId="GZ-1001" f
   romDate="2006-07-13 12:00:00.0"/>

Now item 2 defines a new unique record that supersedes item 1 (based on
fromDate), but that also determines a new applicability (in/over time) as
item 1 is valid from 2001-05-13 up to 2006-5-13. So have following
(hypothetical) current dates into play:

   1. current date = 2000-06-13
   2. current date = 2002-05-13
   3. current date = 2006-06-01
   4. current date = 2006-08-01
   5. current date = 2016-08-09


   - with current date 1: items 1 and 2 are both in the future, both not
   valid at current date
   - with current date 2: item 1 is valid at current date, item 2 is in the
   future
   - with current date 3: item 1 is in the past and item 2 is in the
   future, both not valid at current date
   - with current date 4: item 1 is in the past, and item 2 is valid at
   current date
   - with current date 5: item 1 and 2 are both in the past, but item 2 is
   superseding item 1 and has an no thruDate. This makes item 1 not valid at
   current date but item 2 valid (as it has an open end).

As you can see from above examples, the thruDate is not required to
determine record uniqueness. It only determines applicability in time.

With respect to OFBiz, the UDM regarding the lifespan fields (fromDate -
thruDate) defined by Len Silverston are not applied to its fullest in
various entity definitions to determine the applicability/relationship
between two records from different entities. I have created a JIRA issue
for this (with some sub tasks), see
https://issues.apache.org/jira/browse/OFBIZ-5959

I trust the above will help you understand. Should you have any more
questions and/or remarks, feel free to post them here or contact me
directly.

Best regards,


Pierre Smits

ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services

OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/

On Tue, Aug 9, 2016 at 1:02 AM, Brian Ghidinelli <[hidden email]> wrote:

>
> I'm a big fan of Silverston and his UDM books and have been studying OfBiz
> extensively. I was wondering if anyone can explain why his entities with
> from/thru date use the from date instead of the thru date in the composite
> keys? It seems like you would want to enforce only one version is ever
> active at a time by uniquely indexing the thru date as null (or a magic end
> date, or infinity in Postgres).
>
> I'd be very appreciative of any theory explanations on the use of from
> date instead of thru date?
>
> Brian
>

[Brian Ghidinelli]

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

[SkipDever]

In my experience, thruDate is always null until the entity is expired.  

You therefore get records where now is >= fromDate (or whatever expression you need) and then use the the entityUtil.filterByDate function to remove entities that have expired.

There is perhaps 200 instances of this if you have a search through the java code for "thruDate".

In other words, normally, thruDate is not used (is null) until the entity is expired.  It is created with a fromDate.

-----Original Message-----
From: Brian Ghidinelli [mailto:[hidden email]]
Sent: Tuesday, August 09, 2016 2:58 PM
To: [hidden email]
Subject: Re: Theory of Composite keys with From date vs Thru date?



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