Design Problem - will views solve it?

I have some "internal" complications in the company. The company has about 30 agencies.
Sorry if I have posted too many times.
I don't see it in the newsgroup.

I am building 3 datasources- Admin, Client & HR.

Due to the certain "complications", I have separated all the 30 agencies data into separate datasources. That means each agency will have its own Admin, Client & HR datasources.

The reason for this separation is because each agency may have plans in the near future to develop their own applications to access their databases. Lumping all the agencies data into single datasources may pose a problem as this would give them access to the other agency's data.
I have also highlighted to management of the higher maintenance required to maintain so many datasources and it would be more difficult to create 'company level' reports in a whole. But they still went ahead with the separate datasources option highlighting of the need for the individual agencies needs to develop their own applications. And in the event if they pull out, it would be easier to hand them over their data. Initially, they decided to hire a dedicated staff/dba to maintain the whole database. That's why I did not mind this setup.

But now, it looks like they are not gonna hire anyone. And I'm the only IT guy here.
There's like 300+ people here.
And with 30 agencies and growing, we gonna have like 90 (3x30) datasources to maintain!

Now I am seriously thinking twice of this setup now which I initially proposed. I was wondering, if I gave views for the individual agencies, would they be able to develop on them? Basically all the views should contain something like: "Select * from Table1 where agency_id='agencyA"

If I gave AgencyA "no read" access to Table1, but gave them read access to View1 which accesses table1, would this work?

I know they cannot design the tables. but they could create separate tables to handle any extra fields they need.

Is there anything else important I've missed out in this new setup?

Would this work?
Does Oracle have any special features to handle my problem? I'm no expert in Oracle. I'm just a lone IS guy. Received on Sun Dec 08 2002 - 09:33:59 CST