How do you intend to support I18N then? Not only for categorization, but also for *every* other package in the system?
In a document called "Best Practices for Globalization using the Oracle 9i Internet Application Server" to which I was pointed at by Mohan, Oracle suggests this:
-- fallback language is English which is abbreviated as 'US'.
CREATE VIEW default_message_view
AS SELECT msgid, message
FROM messages
WHERE langid = 'US';
/
-- create view for services, with fall-back mechanism
CREATE VIEW messages_view AS
SELECT d.msgid,
CASE WHEN t.message IS NOT NULL
THEN t.message
ELSE d.message
END AS message
FROM default_view d,
translation t
WHERE t.msgid (+) = d.msgid AND
t.langid (+) = sys_context('USERENV', 'LANG');
SELECT message FROM message_view where msgid = 'hello';
Looks kinda slick:
If you don't find a translation with the first index lookup, you need a second one. Shouldn't be that much of a performance impediment: If an average index lookup takes 5 I/O accesses (that's already for a huge table), you add a maximum of 10 I/Os per row.
It *clearly* is much more expensive than returning the name from a row that Oracle has touched anyway (access costs amount to zero there).
The current I18N solution sacrifices performance for tight coupling of related objects. Is it good? I dunno. Is lose coupling - every translated object gets its own row in acs_objects - better. I dunno.
