connect to customer database
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
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
participants (2)
-
Atanas Karashenski -
Gerold Gruber