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.
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.
Last edited by peter_budo; Nov 9th, 2008 at 5:04 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Unsolved 
