Home » SQL & PL/SQL » SQL & PL/SQL » Handle customer specific objects in Oracle (Oracle, 10/11)
Handle customer specific objects in Oracle [message #571416] Sun, 25 November 2012 17:10 Go to next message
matthiasmax
Messages: 4
Registered: November 2012
Junior Member
the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... Sad )

This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.

Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.

To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme

I would appreciate any hint or best practise. It's an oldschool relational db.

Thank you!
Re: Handle customer specific objects in Oracle [message #571428 is a reply to message #571416] Sun, 25 November 2012 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.


This is one common way.
The other one is to use only ONE schema and build views (or use VPD) upon the tables for each user so they only see their data.

Regards
Michel
Re: Handle customer specific objects in Oracle [message #571463 is a reply to message #571428] Mon, 26 November 2012 03:47 Go to previous messageGo to next message
matthiasmax
Messages: 4
Registered: November 2012
Junior Member
Hi Michel,

thanks for getting back. Some more details: The customer tables look completely different with some having 3 columns others up to 40. They also get fed by us via individual GUI forms to edit the data. So it really is more than just filtering for one or two fields in a huge table that holds all the data.

Re: Handle customer specific objects in Oracle [message #571467 is a reply to message #571463] Mon, 26 November 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The customer tables look completely different with some having 3 columns others up to 40.


It seems there are some flaws in the design.
Anyway, this does not change the principle, you filter the useful column inside the views for each user (type).

Regards
Michel
Re: Handle customer specific objects in Oracle [message #571471 is a reply to message #571467] Mon, 26 November 2012 04:06 Go to previous messageGo to next message
matthiasmax
Messages: 4
Registered: November 2012
Junior Member
Hi Michel, thanks.

What about the individual functions & packages? Some of them contain customer specific logic for calculations etc.
Would you separate them via names (e.g. PACKAGE_CUSTOMER1, 2, 3...) then ? Or would you go the route of schemes with the variety of different kinds of objects ?

Thanks
Re: Handle customer specific objects in Oracle [message #571474 is a reply to message #571471] Mon, 26 November 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use the same principle in procedures but it is more difficult (you have to check the presence of the columns) but if the code is really too different between users, choose to go to one schema per them.

Regards
Michel

[Edit: typo]

[Updated on: Mon, 26 November 2012 05:30]

Report message to a moderator

Re: Handle customer specific objects in Oracle [message #571476 is a reply to message #571474] Mon, 26 November 2012 04:37 Go to previous message
matthiasmax
Messages: 4
Registered: November 2012
Junior Member
Thanks for the great input Michel.
Previous Topic: UNION ALL output in multiple colums
Next Topic: add aging columns to derive total balance overdue
Goto Forum:
  


Current Time: Tue Oct 21 05:04:03 CDT 2014

Total time taken to generate the page: 0.08859 seconds