[otrs] connect to customer database

[Gerold Gruber]

Hi,

after using OTRS for a couple of years now (thanks
to the developing guys and those who gave the ideas
for the concepts for that nice little bunch of bits)
we want do the next step.

Without having had a look at the current docs
(blame me if you feel you must, but that in PM, please)
I'd like to ask the community:
- how can I connect to already available database with
the master data of the customers?
(e.g. I want the information for the customer (data
about the company, how much revenue did we make with
them, for how long are they customer ...) as well
as the information about the "customer user"
(which will only as an exception mean, the he is really
a customer user, who can log in in the sense of OTRS)
such as phone number an so on to be fetched from the
in almost every company available database with the
informations about the customers.
In part directly show in the OTRS mask, but also
available as link to a mask where I could get into
the real (native) management interface for those
data.

Can anybody follow me?

Is this possible already?
Positive/negative experiences?

Best regards

Gerold

[Atanas Karashenski]

Hi Gerold,

There are few ways to integrate your existing customer database with OTRS.
The easiest way is to copy from Kernel/Config/Defaults.pm
$Self->{CustomerUser} and $Self->{CustomerCompany} sections, to place
them in Kernel/Config.pm and to modify them according to your needs.

The drawback in this case is that you have to support different
databases and is not possible to have real foreign keys. I personally
think that foreign keys and their database native support is one of the
most important features for relational databases.

Another solution is to place data from both databases in a single
database. If you use Postgresql this can be done very easy by using
schemas. Schemas allow you to have different name spaces in a single
database, thus having foreign keys and all of the extras. Different
applications can be put into separate schemas so they cannot collide
with the names of other objects. More about database schemas you can
find here: http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

When you have more than one schema, your SQL statements have to use
"Schema.Table" notation or you have to set the variable "search_path".
More on this variable you can find here:
http://www.postgresql.org/docs/8.3/static/ddl-schemas.html#DDL-SCHEMAS-PATH

If you decide to use solutions with database schemas, you have to set
the following in your Kernel/Config.pm:
$Self->{'Database::Connect'} = 'SET search_path TO <your-otrs-schema-name>'.
You still have to configure $Self->{CustomerUser} and $Self->{CustomerCompany} sections in
Kernel/Config.pm

In our case we hold customers data in few tables, so I decided to drop
the tables customer_user and customer_company and to replace them with
views over my tables, having the same column names as the original
tables. In order to be able to update some fields from OTRS interface I
created database rules. More about rules:
http://www.postgresql.org/docs/8.3/static/rules-update.html

I hope this will help.

Best Regards,
Atanas Karashenski