I don't completely agree with all the above as I am having the exact problem as sstaley and feel the db design represents the data well.
I have a timesheet apllication with applicable table structures as follows:
TIMESHEET (
timesheet_id [pk],
employee_id [fk],
end_date,
submitted,
approved
)
TIMESHEET_TASK(
timesheet_task_id [pk],
timesheet_id [fk],
subtask_id [fk],
task_id [fk],
...
)
TASK(
task_id [pk],
task_name
)
SUBTASK(
subtask_id [pk],
task_id [pk],
subtask_name,
...
)
and the corresponding mapping segments are as follows:
TimesheetTask.hbm.xml
Code:
<many-to-one name="subtask" foreign-key="FK_TIMESHEET_TASK_SUBTASK" class="QT.QTSubtask">
<column name="SUBTASK_ID"/>
<column name="TASK_ID"/>
</many-to-one>
<many-to-one name="task" column="TASK_ID" foreign-key="TASK_ID_FK" class="QT.QTTask"/>
Subtask.hbm.xml
Code:
<composite-id>
<key-property name="subtaskId" column="SUBTASK_ID" type="java.lang.String"/>
<key-property name="taskId" column="TASK_ID" type="java.lang.String"/>
</composite-id>
There are some important points to note regarding the data. A subtask cannot logically exists without a corresponding task -> task-to-subtask is a one-to-many relationship. TIMESHEET_TASK must use TASK_ID 2x as a foreign key (this is essentially what I'm asking for help on) since I must be able to add both a subtask and a task upon entering a timesheet_task. Note: tasks are required for adding a timesheet_task while subtasks are not.
I thought about appending a system generated id to the SUBTASK table that would eliminate the problem altogether, however something doesn't sit right with me in creating an id field with no apparent meaningfulness with the data. Any and all thoughts are welcome and appreciated.