How to Map Database Lookup Tables Automatically on Insert (Oracle)

I have existing tables that are pretty much denormalized. There are no lookup tables for things list status, type, country, etc... This original design was done just to simplify the application's access to the database, so there was no performance reason for this denormalization.

This has resulted in tables with tons of duplicate data, and I would like to normalize properly by introducing lookup tables for various status/type/country columns.

Is there some was I can do this in the database (oracle) that would remain transparen开发者_JAVA百科t to clients? Applications would continue to do inserts but the database would map things to the proper lookup tables behind the scenes.

I've been experimenting with a combination of views and triggers that will do the mapping, but it feels like there should be a more automatic way of doing this.

---

In the general case, you can make your changes transparent to the users if you can create updatable views.

1. Normalize a base table to 3NF, BCNF, or 5NF.
2. Rename the original base table.
3. Build an updatable view that has the same name, columns, and rows as the original, denormalized base table.
4. Make sure the permissions on the new view correlate with the permissions on the original base table.
5. Test.
6. Repeat until done.

Any client software that tries to SELECT, INSERT, UPDATE, or DELETE the original base table will hit the updatable view instead. (That's because tables and views share a namespace, and that's not an accident.) The dbms and your supporting code will make sure the Right Thing happens.

Depending on your platform and decomposition, building an updatable view might be easy, and it might be impossible. On Oracle, I think the worst case is that you'd have to write INSTEAD OF triggers to support all the query operations. That's not too bad.

But based on a few months knocking around on SO, I have to say I'm not 100% confident you really need to do this, or that you really want to do this. Post your tables' DDL and representative sample data as SQL INSERT statements, and we can offer better, more concrete suggestions.