Re: Designing people and organization management for Emacs

[Jean Louis <bugs@gnu.support>]

* 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