Can someone please suggest a solution to this CMR problem using compound keys, or point us to a similar example that will help us.

We are using JBoss 3.0 RC 3.

We have messages, which have a type (like a mime-type). These messages belong to exactly one instance (we want to be able to have many 'instances' in the same database, so we can run several different clients against the same database but keep their data separable).

So, there is a many-to-one relationship between MessageBean and MessageTypeBean, and we want to set up allowed 'message-types' on a per 'instance' basis.

The table structure we have is as follows. Many thanks for any help.

table instance:

instance_id numeric
name text

primary key is instance_id

table message:

instance_id numeric
message_id numeric
message_type_id numeric
content text

primary key is (instance_id, message_id)
index on: (instance_id, message_type_id)

table message_type:

instance_id numeric
message_type_id numeric
description text

primary key is (instance_id, message_type_id)

For data integrity we have a foreign key on table message relating to table mesage_type:
(message.instance_id, message.message_type_id) -> (message_type.instance_id, message_type.message_type_id)

MessageBean will have a compound primary key comprising of instanceId and messageId.
MessageTypeBean will have a compound primary key comprising of instanceId and messageTypeId.