5 Replies Latest reply on Jul 5, 2004 5:53 PM by Gavin Lang

    design opinions sought

    Gavin Lang Newbie

      Hello,

      I am not much of a database designer so please forgive the format. I am looking at creating the following structure:

      _TBL_JOB_
      JobID (auto-increment PK)
      ... etc

      _TBL_ACTIVITY_
      Id (auto-increment)
      JobID (FK references TBL_JOB)
      ... etc

      A job can have multiple activities. I assume that the TBL_ACTIVITY table will require ID & JOBID as a compound PK.

      Is it possible to represent this in JBoss as a CMP relationship? I am unsure if it is possible to specify that part of the compound PK is to be auto-incrementing.

        • 1. Re: design opinions sought
          Alexey Loubyansky Master

          Why do you assume compound pk is the requirement?
          Generation (even partly) of compound keys is not supported.

          • 2. Re: design opinions sought
            Raj Subramani Newbie

             

            A job can have multiple activities.


            Is an activity associated with multiple jobs or only a single job?



            Job
            ------
            job_id(pk)
            |
            |
            ^
            Job_Activity_List
            -----------------
            job_id (pk,fk)
            activity_id (pk,fk)
            v
            |
            |
            Activity
            -----------
            activity_id (pk)

            If its the former, then Activity itself has only one primary key (activity_id). You would then create a Job_Activity_List table which has job_id and activity_id as "identifying" non-nullable foreign keys which in effect become compound primary keys of this table. In this case you could autoincrement job_id in table Job and activity_id in table Activity. You would use relation-table mapping in jbosscmp-jdbc.xml descriptor to map the entity relationships between Job and Activity.



            Job
            ------
            job_id(pk)
            |
            |
            ^
            Activity
            -----------
            activity_id (pk)
            job_id(fk)

            If it the latter, then job_id appears as a "non-identifying" foreign key in table Activity which means its does not appear as a primary key in table Activity. When you make it nullable or not is up to you and your business requirements. Again, in this scenario, you could autoincrement job_id in table Job and activity_id in table Activity. You would use a foreign key mapping in jbosscmp-jdbc.xml descriptor to map the entity relationships between Job and Activity.

            Hope this helps.

            Cheers
            -raj


            • 3. Re: design opinions sought
              Gavin Lang Newbie

              thanks for the replies. a friend pointed out that it isn't necessary for activityid to be unique. after a cup of coffee i saw his point and realised that i just wanted a auto-inc value for each activity group, ie:

              job_id act_id
              1 1
              1 2
              1 3
              1 4
              2 1
              2 2
              3 1

              To clarify, a Job has multiple Activities. Activity is a part of 1 Job.

              I have kept the Job(id) and Activity(id) as a compound PK of Activity, after realising it is not possible to have a key part auto-incrementing.

              So what I have done is set it as a plain integer and in my session bean I get a reference to the Job(id), count the activities and add 1 to that when I create the activity:

              public void createActivity(Integer job, String user, String activity,
               Double duration) throws RemoteException, CreateException,
               FinderException {
               JobLocal jobLocal = jobHome.findByPrimaryKey(job);
               Integer activitySeq = new Integer(jobLocal.getActivities().size() + 1);
               actHome.create(job, activitySeq, user, activity, duration);
               jobLocal.setLastModified(new Date());
              }
              


              this will probably cause problems at some stage, I am not happy with it as it is but it is working when I test it.

              • 4. Re: design opinions sought
                Raj Subramani Newbie

                 

                To clarify, a Job has multiple Activities. Activity is a part of 1 Job.


                job_id act_id
                1 1
                1 2
                1 3
                1 4
                2 1
                2 2
                3 1


                If an activity is only associated with one job surely activity_id 1 cannot be associated with job_id's 1, 2 & 3?

                Cheers
                -raj

                • 5. Re: design opinions sought
                  Gavin Lang Newbie

                   

                  If an activity is only associated with one job surely activity_id 1 cannot be associated with job_id's 1, 2 & 3?


                  which is why I made the 2 fields the primary key. The activity(1) related to job(1) is different to the activity(1) related to job(2)