I thought to analyze the input chars to avoid useless searches, for
example, if the digit is EXXXXX, where X is number, it is the
user_code, and i'll search just that field; otherwise if the digit is
an email, i'll look only at the email column.
But, the things get little deeper, with the custom fields.
Like in wordpress, if for example someone need 2 email fields for the
user table, it can set how many custome field he want, this with 2
tables:
-----
CREATE TABLE contact_custom_field (
id serial NOT NULL UNIQUE;
kind varchar(20);
input varchar(10);
);
CREATE TABLE contact_custom_values (
id serial NOT NULL UNIQUE;
id_contact integer NOT NULL REFERENCES contact ON (id);
kind varchar(20) REFERENCES contact_custom_field ON (kind);
value varchar(250)
);
-----
That layout is just a draft;
So, if the user need 2 email fields, i'll have those situation:
table contact_custom_value (i'll need a shorter name ;):
1 |email | manual
table contact_custom_values:
1 | 1 | 20 | email | something@example.com |
And so on for every contact who have 2 emaila address.
And obviusly the 'master search' query must look even in those custom
field (just in some kind of them).
So, the situation is a bit complicated -.-
> What i have done with searches on small strings where the user is unsure
> what they are looking for or the spelling, I do something like this
>
> where substr(company_name,1,length($searchtext$ UserSearchString
> $searchtext$)) ilike $searchtext$UserSearchString $searchtext$
>
> and mix it with soundex. This way the user get a list of possible
> matches with only handful to type characters
>
> Draw back is this type of search is it can't be indexed.