On Jan 30, 2:16 am, "Ben" <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm a Cake newbie so please bear with me. How does one handle
> Effective Dating/Data Aging in Cake? For example, consider the
> following scenario:
>
> I am a sales agent at a company in which each sales agent is in charge
> if handling the sales within one or more territories. I am currently
> assigned to two territories, but one week an associate of mine quits
> and I get assigned a third.
>
> The way I see it, the data design for Cake might look like:
>
> agents
> ---------
> id
> name
>
> territories
> -------------
> id
> name
> agent_id
>
> So, once I get assigned the third territory, it looks as though I've
> had that territory forever! I may get credit for sales that occurred
> in my territory before I was assigned to it (which isn't so bad if I'm
> the sales agent, but if I'm the manager it would!)
>
> In other applications I've seen a composite key (id,effective date)
> that allowed you to not only see changes occur but also match up
> historical data correctly.
>
> Does anyone know how to do this in Cake? It looks like the framework
> makes assumptions about the data structures that won't allow an
> effective date to be part of the key.
>
> Any help is appreciated.
Hi Ben,
This comes down to what you know and when you know/knew it, and is a
generic problem rather than a cake one. However, I have worked with
systems that use what you are describing before (extensively), and
planned on writing a "state" behavior from that experience.
The jist of it is:
Each table has the following fields as a minimum:
id
date_learn_start * Optional To manage only on "what you know" ..
date_learn_end * .. remove any date_learn stuff from the explenation
below
date_effect_start
date_effect_end
You would not use auto incrementing id fields, you would need a
sequence to be able to issue unique ids.
The logic goes like this:
When you create an instance...
use the sequence to get a unique id.
set the date_learn_start to todays date/time
set the date_effect_start to todays date/time unless it's a back/
forward dated update
set the date_learn_end and date_effect_end to the maximum date
When you update an instance...
Create a new row with the same id
set the date_learn_start to todays date/time
set the date_effect_start to todays date/time unless it's a back/
forward dated update
set the date_learn_end and date_effect_end to the maximum date
UPDATE whichever state you are overlapping so there is no overlap
How to link instances...
Same way as before, use the id field in the foreign key. There is no
composite FK.
When you search for an instance...
You would add the following conditions (iirc)
date_learn_start <= now
date_learn_end >= now
date_effect_start <= now
date_effect_end >= now
You should only ever (ever!) get a single row returned with this
logic, if you get more the logic isn't correct or the state data is
corrupted.
So for your example question, your teritories data would look
something like before change over:
*** Initial State, before changes to reflect changeover are done ***
id: 55 (State 1/1)
name: Smallville
agent_id: Bob
date_learn_start 13/12/1999
date_learn_end >= 99/99/9999
date_effect_start <= 13/12/1999
date_effect_end >= 99/99/9999
And once the system has been updated, to take account of Bob leaving:
*** Changes made to give Gerald the teritory as of 1st March when Bob
leaves, data entered today ***
*** Updated first state ***
id: 55 (State 1/2)
name: Smallville
agent_id: Bob
date_learn_start 13/12/1999
date_learn_end >= 29/01/2007
date_effect_start <= 13/12/1999
date_effect_end >= 28/02/2007
*** New second state ***
id: 55 (State 2/2)
name: Smallville
agent_id: Gerald
date_learn_start 30/01/2007
date_learn_end >= 99/99/9999
date_effect_start <= 01/03/2007
date_effect_end >= 99/99/9999
In the case of looking for which agent should be paid for a sale on
1/12/2006 you would search for the agent data with Date_effect
1/12/2006, and date_learn of today in the conditions.
There's an obvious disadvantage to this system and that is
performance, indexes become pretty important as the extra fields are
used in every query on the table.
That's it in a nutshell. I just wrote about something that took most
people working on the system about 6 months to understand correctly
(including me), so 1) I hope it's useful and 2) if it's too confusing
you are certainly not alone.
I'm open to bribes to pump out a behavior to do that sooner rather
than later :D.
Anyway, HTH,
AD7six
Please note:
The manual/bakery is a good place to start any quest for info.
You may get your answer quicker by asking on
the IRC Channel (you can access it with just a browser
here:http://irc.cakephp.org).
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
