0 Replies Latest reply on May 23, 2002 1:41 PM by colinws

    CMR problem using compound keys


      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.