Thread: patterns for database administration
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? Does this idea make any sense at all?
Thanks,
-M@
> 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
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@
> 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: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
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@
> 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 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 | +------------------------------+---------------------------------------------+
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@