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@xxxxxxxxxxx
