For an inventory system on Oracle…looking for best design… pros and cons

Our original design had lookup tables for each code in the database. Eg. Condition code table was a parent table to Asset table with a condition code column. This meant quite a few lookup tables, but the design was relatively easy to implement and understand for db staff and developers.

A new design has been suggested where all codes for all tables are kept in one Code table. (All indicators in one indicator table, all dates in one date table, all remarks in one remark table, and all locations in one location table.)

The code type table would have columns:
Code type - eg condition code
Code type nomen - eg used to describe the condition of the asset.

The code type table would be a parent table to the code identifier table with columns:
Code type eg condition code
Code identifier eg A
Code identifier nomen eg ready to ship
Active ind eg Y

A junction table between each major table and the code identifier table eg (asset code identifier asset) would have columns:

Asset code id pk eg 1111
Asset code id eg A
Asset code type eg condition code
Asset pk eg 777777

The second design is confusing to developers and db staff. It also requires the developers to use the application to enforce codes instead of the database. A retreival that used query one table will now have to query many (> 10?) tables.

I would like to know the pros and cons of using each design.

You pretty well answered your own question... which is a good thing, often if you state a question clearly it answers itself.