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: Designing people and organization management for Emacs Date: Thu, 03 Dec 2020 23:15:31 +0300 Message-ID: Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="11196"; mail-complaints-to="usenet@ciao.gmane.io" To: Help GNU Emacs Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Thu Dec 03 21:16:26 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 1kkv1e-0002il-4C for geh-help-gnu-emacs@m.gmane-mx.org; Thu, 03 Dec 2020 21:16:26 +0100 Original-Received: from localhost ([::1]:39014 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kkv1d-0005Om-4b for geh-help-gnu-emacs@m.gmane-mx.org; Thu, 03 Dec 2020 15:16:25 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:37114) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kkv0s-0005M5-Ub for help-gnu-emacs@gnu.org; Thu, 03 Dec 2020 15:15:38 -0500 Original-Received: from static.rcdrun.com ([95.85.24.50]:37713) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kkv0q-0002JB-GX for help-gnu-emacs@gnu.org; Thu, 03 Dec 2020 15:15:38 -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 00000000002C0007.000000005FC94765.00004E7F; Thu, 03 Dec 2020 20:15:33 +0000 Received-SPF: pass client-ip=95.85.24.50; envelope-from=support1@rcdrun.com; helo=static.rcdrun.com X-Spam_score_int: -16 X-Spam_score: -1.7 X-Spam_bar: - X-Spam_report: (-1.7 / 5.0 requ) BAYES_00=-1.9, HEADER_FROM_DIFFERENT_DOMAINS=0.25, 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:125908 Archived-At: 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)