This content has been marked as final.
Show 5 replies
-
1. Re: design opinions sought
aloubyansky Jul 2, 2004 3:42 AM (in response to gavin9)Why do you assume compound pk is the requirement?
Generation (even partly) of compound keys is not supported. -
2. Re: design opinions sought
rajs1 Jul 2, 2004 5:35 AM (in response to gavin9)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
gavin9 Jul 2, 2004 8:40 PM (in response to 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. -
4. Re: design opinions sought
rajs1 Jul 4, 2004 6:44 AM (in response to gavin9)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
gavin9 Jul 5, 2004 5:53 PM (in response to 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)