design opinions sought

[gavin9]

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.

[aloubyansky]

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

[rajs1]

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

[gavin9]

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.

[rajs1]

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

[gavin9]

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)