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.
primary key is instance_id
primary key is (instance_id, message_id)
index on: (instance_id, message_type_id)
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.