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.