From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Jean Louis Newsgroups: gmane.emacs.help Subject: Re: Designing people and organization management for Emacs Date: Sat, 5 Dec 2020 09:11:01 +0300 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="34520"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mutt/2.0 (3d08634) (2020-11-07) Cc: help-gnu-emacs@gnu.org To: Christopher Dimech Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Sat Dec 05 07:14:47 2020 Return-path: Envelope-to: geh-help-gnu-emacs@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1klQqF-0008tZ-4p for geh-help-gnu-emacs@m.gmane-mx.org; Sat, 05 Dec 2020 07:14:47 +0100 Original-Received: from localhost ([::1]:45214 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1klQqE-0004OX-7D for geh-help-gnu-emacs@m.gmane-mx.org; Sat, 05 Dec 2020 01:14:46 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:38596) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1klQok-0004H4-16 for help-gnu-emacs@gnu.org; Sat, 05 Dec 2020 01:13:15 -0500 Original-Received: from static.rcdrun.com ([95.85.24.50]:33783) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1klQoh-000850-P8 for help-gnu-emacs@gnu.org; Sat, 05 Dec 2020 01:13:13 -0500 Original-Received: from localhost ([::ffff:197.157.0.57]) (AUTH: PLAIN admin, TLS: TLS1.2,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by static.rcdrun.com with ESMTPSA id 00000000002C0006.000000005FCB24F4.00002E8B; Sat, 05 Dec 2020 06:13:08 +0000 Content-Disposition: inline In-Reply-To: Received-SPF: pass client-ip=95.85.24.50; envelope-from=bugs@gnu.support; helo=static.rcdrun.com X-Spam_score_int: 14 X-Spam_score: 1.4 X-Spam_bar: + X-Spam_report: (1.4 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_SBL_CSS=3.335, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: help-gnu-emacs@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Users list for the GNU Emacs text editor List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Original-Sender: "help-gnu-emacs" Xref: news.gmane.io gmane.emacs.help:126007 Archived-At: 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.