On 1/17/19 9:07 AM, Rich Shepard wrote:
> On Thu, 17 Jan 2019, David G. Johnston wrote:
>
>> Yes...though now it just sounds like a flawed data model.
>
> David,
>
> This is what I thought.
>
>> How stuck are you in that regard? Those "future" contacts should have
>> their
>> own records and not be derived via an optional field on an existing
>> record.
>
> My goal is to make a functioning business tracking application for my
> consulting services. Almost all my prior postgres databases hold
> environmental data for statistical and spatio-temporal analyses so
> writing a
> business application is a new experience for me and I want to get it
> correct.
>
>> Put differently, how do you know which activities are completed and
>> which are not?
>
> The direct answer is that a completed activity has a row with either a
> future next-activity date or a null (which is the case when the status of
> that organization or contact is 'no further contact'.)
To be clear the next-activity date = next_contact in the database, correct?
NULL basically means unknown, so having it stand for something is a bit
of a stretch. Seems to me a boolean field of name active to denote
contacts you need to keep up with is in order. Then make the
next_contact field NOT NULL and replace the current NULL values with
'infinity':
https://www.postgresql.org/docs/10/datatype-datetime.html
8.5.1.4. Special Values
As to current situation:
1) Use my previous suggestion.
or
2) WHERE COALESCE(next_contact, 'infinity') BETWEEN '01/01/2019'::date
AND 'today'::date
https://www.postgresql.org/docs/10/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
>
> I should rename the Contacts table as People and the Activities table as
> Contacts. The original names came from a sales management system I used
> as a
> design guide, but they're probably confusing to others as well as to me.
> :-)
>
> I can provide my current schema (eight tables) to the list (perhaps
> as an
> attachment), an individual, or put in on a cloud site and pass the URL.
>
> Thanks,
>
> Rich
>
--
Adrian Klaver
adrian.klaver@aklaver.com
