Designing people and organization management for Emacs

Designing people and organization management for Emacs

[Jean Louis]

I have this already since years and I am using it efficiently. There
is nothing much I ever think about the underlying database.

Purpose of this is to design package with contacts management for
Emacs. Many functions I already have. I am quickly sending emails,
SMS, quickly locating contacts, interacting with them and
similar. There are many tables in the database and contacts is one of
most important one.

Today I would not call it any more "contacts", I would rather like to
call it people. As contact is also organization or company as entity
in itself.

I would change or rename many of columns to be more meaningful.

- some people have too many emails that may not fit into 3 fields or 6
  fields here, maybe I should work with the array of emails

- same for the phone, some have more than 3 phone numbers

It would be best if it all fits in one database table. Not that one
has to enter phones somewhere else and reference it from people's
table. I like to make one package for Emacs that handles people and
groups.

Comments are welcome.

I am especially looking into insights:

- what other entries may be required for people's table?

- how to rename or name columns more meaningful and international? I
  was idiosyncratic in naming columns

- anything what is wrong with the table?

All opinions are appreciated.

Table "public.contacts"
           Column           |            Type             | Collation | Nullable |                    Default                    | Storage  | Stats target |     Description      
----------------------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+----------------------
 contacts_id                | integer                     |           | not null | nextval('contacts_contacts_id_seq'::regclass) | plain    |              | ID
 contacts_datecreated       | timestamp without time zone |           |          | now()                                         | plain    |              | Date created
 contacts_datemodified      | timestamp without time zone |           |          |                                               | plain    |              | Date modified
 contacts_timestamp         | timestamp with time zone    |           |          | now()                                         | plain    |              | Timestamp
 contacts_prefix            | integer                     |           |          |                                               | plain    |              | Prefix
 contacts_firstname         | text                        |           |          |                                               | extended |              | First name
 contacts_additionalnames   | text                        |           |          |                                               | extended |              | Middle names
 contacts_lastname          | text                        |           | not null |                                               | extended |              | Last name
 contacts_suffix            | integer                     |           |          |                                               | plain    |              | Suffix
 contacts_namesformat       | integer                     |           |          | 2                                             | plain    |              | Name's format
 contacts_account1          | integer                     |           |          |                                               | plain    |              | Account
 contacts_account2          | integer                     |           |          |                                               | plain    |              | Company
 contacts_account3          | integer                     |           |          |                                               | plain    |              | Member of
 contacts_leadsource        | integer                     |           |          | 12                                            | plain    |              | Lead source
 contacts_title             | text                        |           |          |                                               | extended |              | Title
 contacts_department        | text                        |           |          |                                               | extended |              | Department
 contacts_birthdate         | date                        |           |          |                                               | plain    |              | Birthdate
 contacts_reportsto         | integer                     |           |          |                                               | plain    |              | Reports to
 contacts_donotcall         | boolean                     |           |          |                                               | plain    |              | Do not call
 contacts_invalid1          | boolean                     |           |          |                                               | plain    |              | Invalid e-mail (1)
 contacts_invalid2          | boolean                     |           |          |                                               | plain    |              | Invalid e-mail (2)
 contacts_invalid3          | boolean                     |           |          |                                               | plain    |              | Invalid e-mail (3)
 contacts_officephone       | text                        |           |          |                                               | extended |              | Office phone
 contacts_mobilephone       | text                        |           |          |                                               | extended |              | Mobile phone
 contacts_homephone         | text                        |           |          |                                               | extended |              | Home phone
 contacts_otherphone        | text                        |           |          |                                               | extended |              | Other phone
 contacts_fax               | text                        |           |          |                                               | extended |              | Fax
 contacts_email1            | text                        |           |          |                                               | extended |              | E-mail (1)
 contacts_email2            | text                        |           |          |                                               | extended |              | E-mail (2)
 contacts_email3            | text                        |           |          |                                               | extended |              | E-mail (3)
 contacts_website           | text                        |           |          |                                               | extended |              | Website
 contacts_blogfeed          | text                        |           |          |                                               | extended |              | Blog feed
 contacts_im1               | text                        |           |          |                                               | extended |              | IM Nick
 contacts_im1type           | integer                     |           |          |                                               | plain    |              | IM Type
 contacts_im2               | text                        |           |          |                                               | extended |              | IM Nick
 contacts_im2type           | integer                     |           |          |                                               | plain    |              | IM (2) Type
 contacts_im3               | text                        |           |          |                                               | extended |              | IM Nick
 contacts_im3type           | integer                     |           |          |                                               | plain    |              | IM (3) Type
 contacts_primaryaddress    | text                        |           |          |                                               | extended |              | Primary address
 contacts_primarycity       | text                        |           |          |                                               | extended |              | Primary city
 contacts_primarypostalcode | text                        |           |          |                                               | extended |              | Primary postal code
 contacts_primarystate      | text                        |           |          |                                               | extended |              | Primary state
 contacts_primarycountry    | integer                     |           |          |                                               | plain    |              | Primary country
 contacts_otheraddress      | text                        |           |          |                                               | extended |              | Other address
 contacts_othercity         | text                        |           |          |                                               | extended |              | Other city
 contacts_otherpostalcode   | text                        |           |          |                                               | extended |              | Other postal code
 contacts_otherstate        | text                        |           |          |                                               | extended |              | Other state
 contacts_othercountry      | integer                     |           |          |                                               | plain    |              | Other country
 contacts_description       | text                        |           |          |                                               | extended |              | Description
 contacts_modifiedusername  | text                        |           | not null | "current_user"()                              | extended |              | Modified by username
 contacts_createdusername   | text                        |           | not null | "current_user"()                              | extended |              | Created by username
 contacts_introducedby      | integer                     |           |          | 1                                             | plain    |              | Introduced by
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (contacts_id)
    "contacts_email_index" btree (contacts_email1, contacts_email2, contacts_email3)
    "contacts_faster_idx" btree (contacts_id, contacts_firstname, contacts_lastname, contacts_email1, contacts_email2, contacts_email3, contacts_account1, contacts_account2, contacts_account3, contacts_primarycountry)
Check constraints:
    "contacts_additionalnames_check" CHECK (contacts_additionalnames !~ '
'::text)
    "contacts_contacts_lastname_check" CHECK (length(contacts_lastname) > 0)
    "contacts_email1_check" CHECK (contacts_email1 !~ '
'::text)
    "contacts_email2_check" CHECK (contacts_email2 !~ '
'::text)
    "contacts_email3_check" CHECK (contacts_email3 !~ '
'::text)
    "contacts_firstname_check" CHECK (contacts_firstname !~ '
'::text)
    "contacts_lastname_check" CHECK (contacts_lastname !~ '
'::text)
Foreign-key constraints:
    "contacts_contacts_account1_fkey" FOREIGN KEY (contacts_account1) REFERENCES accounts(accounts_id)
    "contacts_contacts_account2_fkey" FOREIGN KEY (contacts_account2) REFERENCES accounts(accounts_id)
    "contacts_contacts_account3_fkey" FOREIGN KEY (contacts_account3) REFERENCES accounts(accounts_id)
    "contacts_contacts_im1type_fkey" FOREIGN KEY (contacts_im1type) REFERENCES messengers(messengers_id)
    "contacts_contacts_im2type_fkey" FOREIGN KEY (contacts_im2type) REFERENCES messengers(messengers_id)
    "contacts_contacts_im3type_fkey" FOREIGN KEY (contacts_im3type) REFERENCES messengers(messengers_id)
    "contacts_contacts_introducedby_fkey" FOREIGN KEY (contacts_introducedby) REFERENCES contacts(contacts_id)
    "contacts_contacts_leadsource_fkey" FOREIGN KEY (contacts_leadsource) REFERENCES leadsources(leadsources_id)
    "contacts_contacts_namesformat_fkey" FOREIGN KEY (contacts_namesformat) REFERENCES namesformat(namesformat_id)
    "contacts_contacts_othercountry_fkey" FOREIGN KEY (contacts_othercountry) REFERENCES countries(countries_id)
    "contacts_contacts_prefix_fkey" FOREIGN KEY (contacts_prefix) REFERENCES prefixes(prefixes_id)
    "contacts_contacts_primarycountry_fkey" FOREIGN KEY (contacts_primarycountry) REFERENCES countries(countries_id)
    "contacts_contacts_reportsto_fkey" FOREIGN KEY (contacts_reportsto) REFERENCES contacts(contacts_id)
    "contacts_contacts_suffix_fkey" FOREIGN KEY (contacts_suffix) REFERENCES suffixes(suffixes_id)

Re: Designing people and organization management for Emacs

[Eric S Fraga]

Some people use org-contacts (can be found in the org contrib package)
for this.  I use bbdb.
-- 
Eric S Fraga via Emacs 28.0.50 & org 9.4 on Debian bullseye/sid

Re: Designing people and organization management for Emacs

[Pankaj Jangid]

Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> Some people use org-contacts (can be found in the org contrib package)
> for this.  I use bbdb.

I have very recently switched from bbdb to ebdb.

Re: Designing people and organization management for Emacs

[Jean Louis]

* Eric S Fraga <e.fraga@ucl.ac.uk> [2020-12-04 12:57]:
> Some people use org-contacts (can be found in the org contrib package)
> for this.  I use bbdb.

Yes. I was using text files and spreadsheets, bbdb. Before many years
I switched to database backed management of any data that is
structured. And no, org-contacts or BBDB cannot replace the power of
SQL databases. 204111 contacts are in my database each available at
few key presses related.

Contacts are related to "products", BBDB without much development
cannot provide easy relation to products. Related to "calls" where
none of available Emacs software can initiate calls or handle a call
center with track record when each customer have been called and
why. This is trivial with SQL. Relation to "cashaccounts" to track
financial transaction of each person. Or relation to "comments" to
track who commented on which other piece of information in a database
based Emacs. Or "contactskills" that one can make list of related to
recruitment and hiring of staff. Or "emails", where each email can be
related to contact ID and vice versa. Which "identity" belongs to
which contact? People use private and business identities and
integration of such is not easy. Sometimes by error email is sent from
private identity to business. "locations" are related to people, there
are places we record by using GPS and waypoints that have to be
remembered, "mailings" are sent to people and have to be remembered
which person received which email as not to repeat it. Then "notes",
"opportunities", "relations" to other people, "reminders", "invoices",
"sms" and so on.

Many things may be rapidly developed in Emacs with database
backing. Database backed people and information management is
extremely useful.

I wish Emacs would have at least GDBM built in. Module will come to
GNU ELPA soon for PostgreSQL. Developers of it need help on how to
include it.

When data is in the database it can be easily backed up, shared,
replicated in real time, multi user collaboration is built-in. Groups
of people may enter contacts, organizations, notes, tasks, manage
projects together, and work on separate distant computers while
collaborating on same database information. Much programming becomes
redundant or easy peasy as database has so many features very easy to
implement.

Jean

Re: Designing people and organization management for Emacs

[Christopher Dimech]

> Sent: Friday, December 04, 2020 at 2:38 PM
> From: "Jean Louis" <bugs@gnu.support>
> To: "Eric S Fraga" <e.fraga@ucl.ac.uk>
> Cc: help-gnu-emacs@gnu.org
> Subject: Re: Designing people and organization management for Emacs
>
> * Eric S Fraga <e.fraga@ucl.ac.uk> [2020-12-04 12:57]:
> > Some people use org-contacts (can be found in the org contrib package)
> > for this.  I use bbdb.
>
> Yes. I was using text files and spreadsheets, bbdb. Before many years
> I switched to database backed management of any data that is
> structured. And no, org-contacts or BBDB cannot replace the power of
> SQL databases. 204111 contacts are in my database each available at
> few key presses related.

What would happen for things were database model does not fit the data?

> Contacts are related to "products", BBDB without much development
> cannot provide easy relation to products. Related to "calls" where
> none of available Emacs software can initiate calls or handle a call
> center with track record when each customer have been called and
> why. This is trivial with SQL. Relation to "cashaccounts" to track
> financial transaction of each person. Or relation to "comments" to
> track who commented on which other piece of information in a database
> based Emacs. Or "contactskills" that one can make list of related to
> recruitment and hiring of staff. Or "emails", where each email can be
> related to contact ID and vice versa. Which "identity" belongs to
> which contact? People use private and business identities and
> integration of such is not easy. Sometimes by error email is sent from
> private identity to business. "locations" are related to people, there
> are places we record by using GPS and waypoints that have to be
> remembered, "mailings" are sent to people and have to be remembered
> which person received which email as not to repeat it. Then "notes",
> "opportunities", "relations" to other people, "reminders", "invoices",
> "sms" and so on.
>
> Many things may be rapidly developed in Emacs with database
> backing. Database backed people and information management is
> extremely useful.

Quite correct.

> I wish Emacs would have at least GDBM built in. Module will come to
> GNU ELPA soon for PostgreSQL. Developers of it need help on how to
> include it.
>
> When data is in the database it can be easily backed up, shared,
> replicated in real time, multi user collaboration is built-in. Groups
> of people may enter contacts, organizations, notes, tasks, manage
> projects together, and work on separate distant computers while
> collaborating on same database information. Much programming becomes
> redundant or easy peasy as database has so many features very easy to
> implement.
>
> Jean
>
>

Re: Designing people and organization management for Emacs

[Jean Louis]

* Christopher Dimech <dimech@gmx.com> [2020-12-04 17:52]:
> > Yes. I was using text files and spreadsheets, bbdb. Before many years
> > I switched to database backed management of any data that is
> > structured. And no, org-contacts or BBDB cannot replace the power of
> > SQL databases. 204111 contacts are in my database each available at
> > few key presses related.
> 
> What would happen for things were database model does not fit the
> data?

Model is designed based on data to enter in the future. If data
changes in the future it is very easy to change the model. Good
example with my experience is that few people that I know over long
span of years changed their email addresses multiple times. But I do
like to have capability to keep those obsolete email addresses as they
are still related to the person and emails of the person. By using
person's ID I can quickly access all email files. But if I have only 3
fields for email addresses I have no space for 4th and 5th field.

Some people make extra table for emails that are related to contact. I
like keeping most of information in the contacts (people) table. So
what do I do?

One option is that I simply add new column to the table where I would
store those obsolete email addresses. That column need not be queried
to find the "valid" email address. So I do like this:

ALTER TABLE data1 ADD COLUMN data_emailsobsolete TEXT[];

That solves the problem, there is new column for obsolete emails. Let
me insert few:

admin=# INSERT INTO data1 (data_emailsobsolete) VALUES ('{new@example.com,more@example.com}');
INSERT 0 1
admin=# SELECT data_emailsobsolete FROM data1;
        data_emailsobsolete         
------------------------------------
 
 {new@example.com,more@example.com}
(2 rows)

With that simple SQL instruction problem is solved for many years in
advance as the data influenced the model. But user can change to model
to anything one wants.

To find specific person's ID by using some of obsolete emails is easy:

SELECT contacts_id FROM contacts WHERE 'new@example.com' = ANY (contacts_emailsobsolete);
        data_emailsobsolete         
------------------------------------
 {new@example.com,more@example.com}
(1 row)

This is then used by Emacs Lisp function such as:

(contact-find-id-by-email "new@example.com")

I am often searching for valid emails as such I need to contact
people and I used to have 3 booleans fields which say if email address
is valid. Sometimes they become valid again after not being valid for
some time.

In general databases are very easy to adapt to any models.

Jean

Re: Designing people and organization management for Emacs

[Christopher Dimech]

> Sent: Friday, December 04, 2020 at 4:21 PM
> From: "Jean Louis" <bugs@gnu.support>
> To: "Christopher Dimech" <dimech@gmx.com>
> Cc: "Eric S Fraga" <e.fraga@ucl.ac.uk>, help-gnu-emacs@gnu.org
> Subject: Re: Designing people and organization management for Emacs
>
> * Christopher Dimech <dimech@gmx.com> [2020-12-04 17:52]:
> > > Yes. I was using text files and spreadsheets, bbdb. Before many years
> > > I switched to database backed management of any data that is
> > > structured. And no, org-contacts or BBDB cannot replace the power of
> > > SQL databases. 204111 contacts are in my database each available at
> > > few key presses related.
> >
> > What would happen for things were database model does not fit the
> > data?
>
> Model is designed based on data to enter in the future. If data
> changes in the future it is very easy to change the model. Good
> example with my experience is that few people that I know over long
> span of years changed their email addresses multiple times. But I do
> like to have capability to keep those obsolete email addresses as they
> are still related to the person and emails of the person. By using
> person's ID I can quickly access all email files. But if I have only 3
> fields for email addresses I have no space for 4th and 5th field.

I meant, could users be able to set their own models?

> Some people make extra table for emails that are related to contact. I
> like keeping most of information in the contacts (people) table. So
> what do I do?
>
> One option is that I simply add new column to the table where I would
> store those obsolete email addresses. That column need not be queried
> to find the "valid" email address. So I do like this:
>
> ALTER TABLE data1 ADD COLUMN data_emailsobsolete TEXT[];
>
> That solves the problem, there is new column for obsolete emails. Let
> me insert few:
>
> admin=# INSERT INTO data1 (data_emailsobsolete) VALUES ('{new@example.com,more@example.com}');
> INSERT 0 1
> admin=# SELECT data_emailsobsolete FROM data1;
>         data_emailsobsolete
> ------------------------------------
>
>  {new@example.com,more@example.com}
> (2 rows)
>
> With that simple SQL instruction problem is solved for many years in
> advance as the data influenced the model. But user can change to model
> to anything one wants.
>
> To find specific person's ID by using some of obsolete emails is easy:
>
> SELECT contacts_id FROM contacts WHERE 'new@example.com' = ANY (contacts_emailsobsolete);
>         data_emailsobsolete
> ------------------------------------
>  {new@example.com,more@example.com}
> (1 row)
>
> This is then used by Emacs Lisp function such as:
>
> (contact-find-id-by-email "new@example.com")
>
> I am often searching for valid emails as such I need to contact
> people and I used to have 3 booleans fields which say if email address
> is valid. Sometimes they become valid again after not being valid for
> some time.
>
> In general databases are very easy to adapt to any models.
>
> Jean
>

Re: Designing people and organization management for Emacs

[Eric S Fraga]

Please delete me from the CC list in any responses.  Thank you.
-- 
Eric S Fraga via Emacs 28.0.50 & org 9.4 on Debian bullseye/sid

Re: Designing people and organization management for Emacs

[Pankaj Jangid]

Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> Please delete me from the CC list in any responses.  Thank you.

May be that you would like to try setting your MFT headers. Most email
clients seem to honour that.

I see that you are using Gnus. I have set `message-subscribed-addresses'
to generate MFT headers. But there are many ways to do it. Earlier I
kept a list of subscribed mailing lists in a file. Gnus has a way to
read that as well.

Re: Designing people and organization management for Emacs

[Eric S Fraga]

On Saturday,  5 Dec 2020 at 08:44, Pankaj Jangid wrote:
> Eric S Fraga <e.fraga@ucl.ac.uk> writes:
>
>> Please delete me from the CC list in any responses.  Thank you.
>
> May be that you would like to try setting your MFT headers. Most email
> clients seem to honour that.

Thank you.  Yes, I guess I could do this; I'm reading using NNTP and
don't even know the mail address of this list (I can look it up, of
course).  But not a big deal generally.

-- 
Eric S Fraga via Emacs 28.0.50 & org 9.4 on Debian bullseye/sid

Re: Designing people and organization management for Emacs

[Jean Louis]

* Christopher Dimech <dimech@gmx.com> [2020-12-04 19:12]:
> > Model is designed based on data to enter in the future. If data
> > changes in the future it is very easy to change the model. Good
> > example with my experience is that few people that I know over long
> > span of years changed their email addresses multiple times. But I do
> > like to have capability to keep those obsolete email addresses as they
> > are still related to the person and emails of the person. By using
> > person's ID I can quickly access all email files. But if I have only 3
> > fields for email addresses I have no space for 4th and 5th field.
> 
> I meant, could users be able to set their own models?

Users can always design their database tables or models.

In relation to this project "people" I am now polishing the table
unless there are no comments in the way how I think is best. Then
users can adapt table if they wish to or make their own functions to
enhance.

Let us say there table has:

people_firstname
people_middlenames
people_lastname

For myself only lastname is required, and there can be anything like
even email address, as some people are unknown. It can be
'Unknown'. In some countries people must have first name, maybe
somebody wish to make it obligatory so they can impose that on the
database level and on the Emacs Lisp level.

Let us say somebody does not want to use those columns at all but just
one column, that person can add it:

ALTER TABLE people ADD COLUMN people_name TEXT;

and then other columns need not be used.

But my system is enough general that will allow designing any table
and then editing such table. The minimum requirement would be to keep
for every table _id like people_id with unique number.

First we have to have good contacts management that is upgradable with
other tables.

Jean

Re: Designing people and organization management for Emacs

[Christopher Dimech]

> Sent: Friday, December 04, 2020 at 5:26 PM
> From: "Jean Louis" <bugs@gnu.support>
> To: "Christopher Dimech" <dimech@gmx.com>
> Cc: help-gnu-emacs@gnu.org, "Eric S Fraga" <e.fraga@ucl.ac.uk>
> Subject: Re: Designing people and organization management for Emacs
>
> * Christopher Dimech <dimech@gmx.com> [2020-12-04 19:12]:
> > > Model is designed based on data to enter in the future. If data
> > > changes in the future it is very easy to change the model. Good
> > > example with my experience is that few people that I know over long
> > > span of years changed their email addresses multiple times. But I do
> > > like to have capability to keep those obsolete email addresses as they
> > > are still related to the person and emails of the person. By using
> > > person's ID I can quickly access all email files. But if I have only 3
> > > fields for email addresses I have no space for 4th and 5th field.
> >
> > I meant, could users be able to set their own models?
>
> Users can always design their database tables or models.
>
> In relation to this project "people" I am now polishing the table
> unless there are no comments in the way how I think is best. Then
> users can adapt table if they wish to or make their own functions to
> enhance.

Would need some useful description of making new tables and some functions
that would help users do that.  At least spare them some of the process.

> Let us say there table has:
>
> people_firstname
> people_middlenames
> people_lastname
>
> For myself only lastname is required, and there can be anything like
> even email address, as some people are unknown. It can be
> 'Unknown'. In some countries people must have first name, maybe
> somebody wish to make it obligatory so they can impose that on the
> database level and on the Emacs Lisp level.
>
> Let us say somebody does not want to use those columns at all but just
> one column, that person can add it:
>
> ALTER TABLE people ADD COLUMN people_name TEXT;
>
> and then other columns need not be used.
>
> But my system is enough general that will allow designing any table
> and then editing such table. The minimum requirement would be to keep
> for every table _id like people_id with unique number.
>
> First we have to have good contacts management that is upgradable with
> other tables.
>
> Jean
>
>

Re: Designing people and organization management for Emacs

[Jean Louis]

* Christopher Dimech <dimech@gmx.com> [2020-12-04 19:42]:
> > In relation to this project "people" I am now polishing the table
> > unless there are no comments in the way how I think is best. Then
> > users can adapt table if they wish to or make their own functions to
> > enhance.
> 
> Would need some useful description of making new tables and some functions
> that would help users do that.  At least spare them some of the process.

When preparing Emacs package let us say for people's contact
management then users do not need to think much of the underlying
database table design. They think of people's names, birthdate,
address, and contact information.

Programmers need to know how to make functions or do SQL queries.

Some references:

https://aiven.io/blog/an-introduction-to-postgresql

https://www.postgresqltutorial.com/

Re: Designing people and organization management for Emacs

[Christopher Dimech]

> Sent: Friday, December 04, 2020 at 6:56 PM
> From: "Jean Louis" <bugs@gnu.support>
> To: "Christopher Dimech" <dimech@gmx.com>
> Cc: help-gnu-emacs@gnu.org
> Subject: Re: Designing people and organization management for Emacs
>
> * Christopher Dimech <dimech@gmx.com> [2020-12-04 19:42]:
> > > In relation to this project "people" I am now polishing the table
> > > unless there are no comments in the way how I think is best. Then
> > > users can adapt table if they wish to or make their own functions to
> > > enhance.
> >
> > Would need some useful description of making new tables and some functions
> > that would help users do that.  At least spare them some of the process.
>
> When preparing Emacs package let us say for people's contact
> management then users do not need to think much of the underlying
> database table design. They think of people's names, birthdate,
> address, and contact information.
>
> Programmers need to know how to make functions or do SQL queries.

Would things be general enough with your plan?  For instance,
settings fields and naming them, on which the queries would run.

Can test it out and ask others using it for their work.  Have some contacts
at the Osservatorio Vesuviano, Naples (Italy).

> Some references:
>
> https://aiven.io/blog/an-introduction-to-postgresql
>
> https://www.postgresqltutorial.com/
>
>

Re: Designing people and organization management for Emacs

[Jean Louis]

* Christopher Dimech <dimech@gmx.com> [2020-12-04 21:06]:
> > When preparing Emacs package let us say for people's contact
> > management then users do not need to think much of the underlying
> > database table design. They think of people's names, birthdate,
> > address, and contact information.
> >
> > Programmers need to know how to make functions or do SQL queries.
> 
> Would things be general enough with your plan?  For instance,
> settings fields and naming them, on which the queries would run.

That is what I do now mostly. I can create any table and then add,
edit, delete records. But it is not perfect, it needs work.

There will be also simpler version to create your own tables similar
like a wizard. Then users can add, edit, delete records or search
them.

> Can test it out and ask others using it for their work.  Have some contacts
> at the Osservatorio Vesuviano, Naples (Italy).

Database may be used by any language on any operating system while
Emacs Lisp is for Emacs only and there is  web browser interface as
well.

I have many tables and views defined and Hyperscope dynamic knowledge
repository development is simplifying some other tables.

Node in Hyperscope can be anything, for example WWW hyperlink that is
annotated or has description. It can be note or action like a task. By
using types one can then define anything. In that sense the table
"notes" is dropped and replaced with hyperdocument that may be "note",
table "tasks" is dropped and replaced with hyperdocument that may be
"action". Based on the principle of types, then the table "people"
should have pretty extensible design or model that will stand the
future in the sense that users may define their own types for table
columns.

Example would be to let user define type of contact to be "Fediverse"
with the value @somebody@example.com and then user can make function
how to communicate to such contact.

Re: Designing people and organization management for Emacs

[Christopher Dimech]

> Sent: Friday, December 04, 2020 at 7:23 PM
> From: "Jean Louis" <bugs@gnu.support>
> To: "Christopher Dimech" <dimech@gmx.com>
> Cc: help-gnu-emacs@gnu.org
> Subject: Re: Designing people and organization management for Emacs
>
> * Christopher Dimech <dimech@gmx.com> [2020-12-04 21:06]:
> > > When preparing Emacs package let us say for people's contact
> > > management then users do not need to think much of the underlying
> > > database table design. They think of people's names, birthdate,
> > > address, and contact information.
> > >
> > > Programmers need to know how to make functions or do SQL queries.
> >
> > Would things be general enough with your plan?  For instance,
> > settings fields and naming them, on which the queries would run.
>
> That is what I do now mostly. I can create any table and then add,
> edit, delete records. But it is not perfect, it needs work.

That,s been my concern.  But one will have to see.

> There will be also simpler version to create your own tables similar
> like a wizard. Then users can add, edit, delete records or search
> them.

You are getting to something then.  I'm pleased to hear that.

> > Can test it out and ask others using it for their work.  Have some contacts
> > at the Osservatorio Vesuviano, Naples (Italy).
>
> Database may be used by any language on any operating system while
> Emacs Lisp is for Emacs only and there is  web browser interface as
> well.

They got to use emacs, of course.

> I have many tables and views defined and Hyperscope dynamic knowledge
> repository development is simplifying some other tables.
>
> Node in Hyperscope can be anything, for example WWW hyperlink that is
> annotated or has description. It can be note or action like a task. By
> using types one can then define anything. In that sense the table
> "notes" is dropped and replaced with hyperdocument that may be "note",
> table "tasks" is dropped and replaced with hyperdocument that may be
> "action". Based on the principle of types, then the table "people"
> should have pretty extensible design or model that will stand the
> future in the sense that users may define their own types for table
> columns.

That's the real beef!

> Example would be to let user define type of contact to be "Fediverse"
> with the value @somebody@example.com and then user can make function
> how to communicate to such contact.

Ok.  I'm excited about this.

Re: Designing people and organization management for Emacs

[Jean Louis]

* Christopher Dimech <dimech@gmx.com> [2020-12-04 22:44]:
> > > Can test it out and ask others using it for their work.  Have some contacts
> > > at the Osservatorio Vesuviano, Naples (Italy).
> >
> > Database may be used by any language on any operating system while
> > Emacs Lisp is for Emacs only and there is  web browser interface as
> > well.
> 
> They got to use emacs, of course.

Database data management is independent of the software or interface
to database. In general such contacts address book can be managed also
by using other editors as long as such allow execution of external
programs as majority do not have the internal programming language
like Emacs Lisp.

Example is list of assigned tasks to user, such task could be assigned
by user in one city and to be seen, viewed or updated by the viewer in
other city or on distance. The user updating task could be doing it by
mobile phone, or java application, or by using web browser or by using
other programming language, or Gtk widgets or other interface.

Re: Designing people and organization management for Emacs

[Jean Louis]

rcdbusiness=# \d+ people
                           Table "public.people"
        Column         |           Type           |      Description      
-----------------------+--------------------------+-----------------------
 people_id             | integer                  |  ID
 people_datecreated    | timestamp with time zone |  Date created
 people_datemodified   | timestamp with time zone |  Date modified

It will be always known when a record has been modified
automatically. Additionally there will be version control on
modifications. 

 people_prefix         | integer                  |  Prefix

This is like Dr. Mr. Mrs. etc. where users may leave it empty or add
their new names prefixes.

 people_firstname      | character varying(255)   |  First name
 people_middlenames    | text                     |  Middle names
 people_lastname       | text                     |  Last name

In some countries people have one name, or many names. Only "Last
 name" is required but it may also mean "Name" itself.
 
 people_suffix         | integer                  |  Suffix

This is name suffix such as Sr. Jr. Esq. and similar or combinations
of it. Some insist on having it. It is better to honor it.

 people_account1       | integer                  |  Account
 people_account2       | integer                  |  Company
 people_account3       | integer                  |  Member of

I was calling main groups "accounts" and people "contacts". But the
meaning is lost, so it is better calling people "people" and contacts
can be anything such as contact of organization or police. But
"police" is entity and not an individual. It is nicer to refer to
individuals as people.

But how to refer to general organizations as they are not always
organizations?

Referring to such as "accounts" is not proper. I wish to rename
it. Accounts have its types:

 accounttypes_id | accounttypes_name 
-----------------+-------------------
               1 | Analyst
               2 | Competitor
               3 | Customer
               4 | Integrator
               5 | Investor
               6 | Partner
               7 | Press
               8 | Prospect
               9 | Reseller
              10 | Managed
              11 | Our company
              12 | Accounting
              13 | Autoresponder
              14 | Mailing List

that may be changed. Account was also "account" in bookkeeping, but I
would separate it.

Maybe is good to name it "listofpeople" that may be:

- simple list
- organization
- company
- mailing list

And I have kept it with 3 groups, account is main list kept in the
database such as "People who wish to work with me", "company" is their
company, and they could be "member" of some third list which could be
"family". This sufficed for more than 15 years.

Additionally I have table "mailinglists" that can unify other mailing
lists into one.

I will most probably rename it to "listofpeople"

There is other table names "groups" which allows itself to be groups
of anything such as groups of products or groups of people.

 people_leadsource     | integer                  |  Lead source
 people_title          | text                     |  Title
 people_department     | text                     |  Department
 people_birthdate      | date                     |  Birthdate

One also need the deathdate, but for new that can be left in description.

 people_reportsto      | integer                  |  Reports to

If person reports to another individual

 people_dontcontact    | boolean                  |  Do not call
 people_invalid1       | boolean                  |  Invalid e-mail (1)
 people_invalid2       | boolean                  |  Invalid e-mail (2)
 people_invalid3       | boolean                  |  Invalid e-mail (3)

If email is invalid

 people_officephone    | text                     |  Office phone
 people_mobilephone    | text                     |  Mobile phone
 people_homephone      | text                     |  Home phone
 people_otherphone     | text                     |  Other phone
 people_fax            | text                     |  Fax

I may need additional column people_morephones to be array as some of
them do have more phones than just 2-3

 people_email1         | text                     |  E-mail (1)
 people_email2         | text                     |  E-mail (2)
 people_email3         | text                     |  E-mail (3)

Any column described in the main table is queried and search more
frequently. There will be additional "contacts" table where additional
people's contacts can be entered arbitrarily as some people have more
than 2 addresses or more emails, websites, social networks and similar
But those are not queried or used frequently.

Among 204,113 people 950 of them have 2 email addresses and 127 of
them have 3 email addresses.

 people_website1       | text                     |  Website
 people_website2       | text                     |  Other website

 people_contact1       | text                     |  Other contact (1)
 people_contacttype1   | integer                  |  Contact type (1)
 people_contact2       | text                     |  Other contact (2)
 people_contacttype2   | integer                  |  Contact type (2)
 people_contact3       | text                     |  Other contact (3)
 people_contacttype3   | integer                  |  Contact type {3}
 
These are other ways to contact somebody such as social networks,
other phones, emails. That means 3 emails, 3 phones, fax, plus
possibly 3 other ways to contact person. Additional table could be
made but some most important contacts are better be kept in main table
for faster querying.

 people_address1       | text                     |  Primary address
 people_city1          | text                     |  Primary city
 people_zip1           | text                     |  Primary postal code
 people_state1         | text                     |  Primary state
 people_country1       | integer                  |  Primary country
 people_address2       | text                     |  Other address
 people_city2          | text                     |  Other city
 people_zip2           | text                     |  Other postal code
 people_state2         | text                     |  Other state
 people_country2       | integer                  |  Other country

Rarely somebody has more than 2 addresses, but many do have 2.

 people_description    | text                     |  Description
 people_usermodified   | text                     |  Modified by username
 people_usercreated    | text                     |  Created by username

When table is modified in collaboration, every user will know WHEN
LAST TIME was modified LAST TIME and BY WHICH USER LAST TIME, and by
using automated version control one will know each modifications in
past by time.
 
people_introducedby   | integer                  |  Introduced by

This is important to know WHICH INDIVIDUAL brought somebody else. When
doing marketing people need to get awarded for those who they
introduce. It is also important in relations to know where this person
comes from, who introduced the person.

Table prefixes refers to name-prefixes, maybe it can be renamed in
future.

select * from prefixes;
 prefixes_id | prefixes_name 
-------------+---------------
           1 | Mr.
           2 | Mrs.
           3 | Ms.
           4 | Dr.
           5 | Prof.
           6 | Miss
           7 | Ing.
           8 | Sir.
           9 | Eng.
          10 | Prof. Dr.
          11 | Dr.med.
(11 rows)

Table suffixes:

select * from suffixes;
 suffixes_id | suffixes_name 
-------------+---------------
           1 | I
           2 | II
           3 | III
           4 | Jr.
           5 | Sr.
           6 | dipl. oec.
           7 | Esq.
           8 | D.C.
           9 | PhD

select * from contacttypes;
 contacttypes_id |         contacttypes_name         | contacttypes_command 
-----------------+-----------------------------------+----------------------
               2 | MSN                               | 
               3 | Yahoo messenger                   | 
               4 | Google messenger                  | 
               5 | E-bay                             | 
               6 | ICQ                               | 
              13 | Fax number                        | 
              14 | Mobile phone                      | 
              15 | XMPP or Jabber ID                 | 
              16 | Fediverse                         | 
               9 | E-mail                            | 
              17 | Old not functional e-mail address |

Command may be used to launch some specific software.

Some contacts such as "Skype" I am not even entering as I tell them
that we do not use proprietary software.

Among 200,000+ contacts, there are 90,000+ using Gmail which is major
privacy issue for the whole world. I was thinking to automatically
include special gmail related signature into emails to warn people and
help them switch to their own email address.

Comments are welcome.

Next will be to design "peoplelist" table, or maybe I should just
leave it as "accounts" as in the meaning:

3. (5) account, business relationship -- (a formal contractual relationship established to provide for regular banking or brokerage or business services; "he asked to see the executive who handled his account")

Not so sure if it is proper.