Design question (schemas)
 

First off, I'm not really a DBA... I'm learning as I go. The office I work in has a lot of problems when it comes to the integrity and reliability of data stored in our Oracle database. One of the little things I'm wanting to resolve is the duplication of lookup values across different applications.

Everytime a new feature is added, new lookup tables have typically been created and nothing is reused. Then, later, everyone complains when data in different applications doesn't marry up (well durr... you changed the lookup value for one app, but not another, or you allowed the user to manually enter data introducing mispellings and nonstandard values).

Sometimes, new lookups are created because at the time, no one is aware that a lookup table for X purpose already exists - or where it exists.

So I was thinking of creating a "reference" or "lookup" or "standard" schema for the various standards and lookup tables that we use across multiple applications (we typically have created a new schema for a new application). All of these applications are really a part of one "enterprise system" - only they've been built in a sort of "stovepipe" way such that while it would be tremendously advantageous for them to more easily share data, they don't. I'm tired of reinventing the world every time a project comes along for some new tool.

Is this a good idea or bad?

Re: Design question (schemas)
 

Sounds like a good idea to me. Once you have created the lookuptables in this schema you can simply grant select on the table to the other application schemas. It can then be referenced by

select * from schema_name.table_name;

or you could create a synonym to g=to get rid of the schema name.

Re: Design question (schemas)
 

What about using only one look up table per application.