Thread: Database organization questions

I am going to be setting up OTRS, a help desk application, and the backend is going to be Postgres, along with this I need to create a separate application to track whether or not a customer has a service contract with us and the type of service contact.  The question I have is that the service application will have a table that stores the customer's contact information, and OTRS also has this table, is there a way I can share this table between these two applications?  I want them both to access the same customer table but I was going to try and split the applications into multiple db's but still on one server.  Is this necessary, or even logical?  This is a proof of concept for my company and further down the line if this goes well, all of the company's data could be ported from M$ Access to this system so I need to keep that in mind.  Each group(Sales, Engineering,Manufacturing) would have it's own database but need to share tables such as customer or employee, I have no problem having one database for the whole company and then using FK's to ensure integrity but I would rather have each department have it's own separate database, and then a specified intervals possibly pull from a master db.  This is a small company < 50 people so the load at any one time would not be excessive.

On Thu, Jun 16, 2011 at 08:50:30AM -0400, matty jones wrote:
> I am going to be setting up OTRS, a help desk application, and the backend
> is going to be Postgres, along with this I need to create
> a separate application to track whether or not a customer has a service
> contract with us and the type of service contact.  The question I have is
> that the service application will have a table that stores the customer's
> contact information, and OTRS also has this table, is there a way I can
> share this table between these two applications?

Do the two applications have the same concept for this table? In other words,
do they both expect the same table structure, field names, data types, field
order, indexing, etc.? If not, it will be pretty tough to get them to share
the table.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

What I think I am going to do is design the table structure for my db and then use a trigger to update the otrs tables(kept separate) when necessary.  If I mixed the tables then I could potentially run into issues when I do an upgrade of otrs or if I need to change something on my end down the line.  Keeping them separate seems to make more sense to me right now.

On Mon, Jun 27, 2011 at 3:17 PM, matty jones <urlugal@gmail.com> wrote:

> What I think I am going to do is design the table structure for my db and
> then use a trigger to update the otrs tables(kept separate) when necessary.
>  If I mixed the tables then I could potentially run into issues when I do an
> upgrade of otrs or if I need to change something on my end down the line.
>  Keeping them separate seems to make more sense to me right now.

Keeping them separate makes lots of sense. Modularity is an important
design pattern for enterprise data and the services around them.

Technically, there are many ways to implement separation. Triggers
work, but it would be a design decision that forces you to keep those
two services on the same system.

I would recommend using a technology that allows more flexibility for
future placement of databases. That would be a generalised replication
solution, though you might generalise further and adopt an ETL or real
time integration tool.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services