Re: design of application / schema / multiple development schemas

bdj wrote:
> Hello!
> Can anyone tell me where to read more about best practices about this:
>
> Should I put data in a seperate scheme for tables,
> packages in anoter schema
> and create a lot of users that have access to (some) packages.

Security manuals, probably. And yes, the above scenario is valid in some cases.
For one, it is relative easy to split the app into a database part (tables, table API) and an application server part (users/access and the replacement for the packages, like Java).

The general consensus seems to be to 'let the database do, what the database does best', and that would be process data. So, do *not* fetch record-for-record into an array on the app. server, and sort it there - let Oracle do that. You might find it easy to use packages for that purpose, maybe in addition to the TAPI, and whatever you use on the app. server.
>
> When the tables are in anoter schema than the packages, where should the
> table-API (generated from Designer) be?
I would see that as one complete unit, so the API would be in the same schema as the tables. You may find otherwise, and separate the TAPI from the tables (and possibly, from the interface packages).

>
> Or should i just put data and package in the same schema an create a lot of
> users that have access to (some) packages?

What suits your environment/client(s) best. It all depends.

>
> How to support multiple developer-teams that wish to access the same tables
> during development (they should work with different sets of data)?
> One (bad) 'solution' is having multiple instances with its own
> database-files on the same machine, but then you will get an
> performance issue by wasting memory.

I find that a perfectly acceptable solution for development, if the above is the case. Actually, I do not see a single instance scenario, where different teams will access that *same* tables.

Multiple instances will allow for typical development scenarios like bouncing without disrupting the other workers. Also, source control would be tighter, as you may force the delivery of installation/modification scripts over the instances.

I would worry about your code base/source control, having different groups of developers, working on the same product.

-- 
Regards,
Frank van Bortel