patterns for database administration

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application? Does this idea make any sense at all?

You could give the administrators access to all of the tables and just give
the customer access to views only. That would be one possibility, I think.

Chris

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application? Does this idea make any sense at all?

This sounds like a task for separate schemas rather than for two
separate databases. The advantage of the former is that you can
move data between the two in SQL fairly easily, moving data between
two independent databases is (currently) more challenging.
--
Mike Nolan

On Mar 23, 2004, at 10:53 AM, Mike Nolan wrote:

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application? Does this idea make any sense at all?

This sounds like a task for separate schemas rather than for two
separate databases. The advantage of the former is that you can
move data between the two in SQL fairly easily, moving data between
two independent databases is (currently) more challenging.

One of the reasons this idea was suggested was because my client is
concerned that its "crazy" to be modifying business data in a system
that is running and processing purchase transactions. And I'm
wondering whether or not this is even a concern when most people build
this type of application. I think its going to be painful to keep
track of changes between the two databases (or schemas if you prefer).
It sounds like this would be highly prone to errors and cause more
problems than it solves.
Thoughts?
-M@

On Mar 23, 2004, at 10:35 AM, Chris Boget wrote:

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application? Does this idea make any sense at all?

You could give the administrators access to all of the tables and just
give
the customer access to views only. That would be one possibility, I
think.

This is an EJB application that is already up and running in the real
world. Refactoring it as you suggest is not an option for us at this
time.
-M@

Chris Boget wrote:

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application? Does this idea make any sense at all?

I have a system I wrote that does this. Occasionally, the source from which
we get the data pushes an update, and (frankly) I didn't trust the source to
be consistent, so I process the update as if it were new, raw data, put all
the results into temp tables, then use a stored procedure to copy all the
date to the live tables within a single transaction.

In my case, the database is relatively small (less than 100,000 rows total,
in 6 tables, I think) so this happens fairly quickly.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

One of the reasons this idea was suggested was because my client is
concerned that its "crazy" to be modifying business data in a system
that is running and processing purchase transactions. And I'm
wondering whether or not this is even a concern when most people build
this type of application. I think its going to be painful to keep
track of changes between the two databases (or schemas if you prefer).
It sounds like this would be highly prone to errors and cause more
problems than it solves.
Thoughts?

It sounds like the problem they have is that they want you to be able to
make changes, but perhaps not make them active until they are all
finished. Is that what the problem is?

This can be solved in a number of ways. You can mark records as
"testing", and then have an approval step which copies the testing records
over the production records. You can also have an "active date" on your
records, and then mark your records as being active in the future.

I think we need more information on the "whys" of this before making
clearer suggestions.

Jon

On Mar 23, 2004, at 11:16 AM, Bill Moran wrote:

Chris Boget wrote:

Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that
data would be pushed out to the other database running the customer
web application? Does this idea make any sense at all?

I have a system I wrote that does this. Occasionally, the source from
which
we get the data pushes an update, and (frankly) I didn't trust the
source to
be consistent, so I process the update as if it were new, raw data,
put all
the results into temp tables, then use a stored procedure to copy all
the
date to the live tables within a single transaction.

In my case, the database is relatively small (less than 100,000 rows
total,
in 6 tables, I think) so this happens fairly quickly.

Are your updates mostly new data that is getting added to the
production system? Or are people modifying rows that already exist in
the production system? In my case it will be a mix of both. Lots of
new rows, but always the possibility for existing rows to be modified.
-M@

On Mar 23, 2004, at 11:54 AM, Jonathan Bartlett wrote:

One of the reasons this idea was suggested was because my client is
concerned that its "crazy" to be modifying business data in a system
that is running and processing purchase transactions. And I'm
wondering whether or not this is even a concern when most people build
this type of application. I think its going to be painful to keep
track of changes between the two databases (or schemas if you prefer).
It sounds like this would be highly prone to errors and cause more
problems than it solves.
Thoughts?

It sounds like the problem they have is that they want you to be able
to
make changes, but perhaps not make them active until they are all
finished. Is that what the problem is?

This can be solved in a number of ways. You can mark records as
"testing", and then have an approval step which copies the testing
records
over the production records. You can also have an "active date" on
your
records, and then mark your records as being active in the future.

Indeed we're already doing that.

I think we need more information on the "whys" of this before making
clearer suggestions.

I agree completely. Unfortunately I don't have anything more concrete
to go on than my previous post above. I think the "whys" are
extremely weak. I'm going to suggest we leave things as they are and
allow the administration application to update the production database.
Thanks,
-M@

On 23/03/2004 18:24 Matthew Hixson wrote:

This question isn't specific to Postgres, but since I'm already on this
list and there are knowledgeable people here I thought I'd ask. I'm
currently working on a project that has a web application that faces the
customer and a web application that faces the administrators. They sit
on top of the same Postgres instance. This database also keeps track of
purchase information for sales made on the customer website.
Has anyone ever heard of using a separate administration database
which is used to modify business data, and then periodically that data
would be pushed out to the other database running the customer web
application?

Yes. I've worked on such applications.

Does this idea make any sense at all?

It's often a pragmaitic decision taken because the back-end systems are
not able to easily make the data available to the web app. Often an Oracle
DB is placed between the web app and the back-end systems.

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+