Hi,
I'm having trouble with mapping a relationship. I have a table with 3 fk's.
2 of share the same column. The way I have this mapped is not working.

The mapping:

<many-to-one
class="Patient"
name="Patno"
not-null="true"
>
<column name="patno" />
<column name="internal_protocol_id" />
</many-to-one>
<many-to-one
class="Protocol"
name="InternalProtocol"
not-null="true"
>
<column name="internal_protocol_id" />
</many-to-one>

The error:
ERROR: Repeated column in mapping for class com.fujisawa.webforms.app.Form
should be mapped with insert="false" update="false": internal_protocol_id


Thanks,
-Scott

This error means you mapped this field "internal_protocol_id" 2 times in your mapping file.
You can't map it two times, so in one of them you have to specify insert and update as false.
Or if you mapped it wrong take it out.

Hope this works

If do specify it as update false for one of the mappings, will I not take the risk of violating a fk constraint for that mapping? I would think it would be common to have a colum in one table be a foreign key to more than one table. You have to map it once for each relationship, correct? I didn't find any examples of this in the users guide.

I believe foreign keys should each refer only to one table. That's the purpose of them: the key refers to a unique row, which of course cannot exist in two tables at once.
Why not add another column for the other relation?

It is very seldom (read broken model) to have one fk point to two different entities. What decides which entity is the right one ? Or are they both valid at the same time ? ...

I'll try to explain more clearly. The table has 2 foreign key constraints; each to different tables. Once of them is composed of column A and B and the other is only column A.

Is the model broken to conceptually want to only allow rows in a table if there are existing rows in 2 other tables?

It *is* a broken data model I think. Properly normalized models don't have this, I'm pretty certain.

But, its easy to handle, just follow the instruction in the exception message.

Quote:

"Repeated column ... should be mapped with insert="false" update="false": internal_protocol_id"

Ok, makes sense. I'm working with an old sybase database and it's not normalized at all. THanks for all the help.

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


Subtask.hbm.xml



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.

I guess I would recommend doing things a simply as possible. This kind of design seems needlessly complex, as there are straightforward ways of modeling such relationships.

My questions about your design are:

1) Are task and subtask in the same table? If so, why, if they are different entities storing differing information? If not, why can't subtask use its subtaskId field as an id instead of the composite-id?

2) If subtasks are children of tasks, why does the TimetableTask have a collection of them? Shouldn't they be accessed through the Task?

Perhaps I am misunderstanding. What do your tables look like?

The table structures are listed in my first post above the hibernate mappings. I've been staring at this for a couple days now so my fresh perspective has somewhat dissolved.
1) The answer is yes and no, as you'll see from the table structures. Task's CAN exist without subtasks, but not vice-versa. Similarly, you can attach a task to a timesheet_task (a timesheet_task represents a task that has been attached to a timesheet with additional information regarding the date and billlable options and furthermore references a table containing the # of hours worked on that task item for a given day of the week), but you cannot attach a subtask without a task. Here is some example data that will perhaps make this a bit easier to visualize.

Administration Tasks Screen:
DEV - Development
MKT - Marketing
ADMIN - Administration

Administration Subtasks Screen:
(if you select DEV - Development as the parent task from a drop-down menu, you'd have a listing of the corresponding subtask options)
BUG - Bug Fix
CLER - Clerical
CODE - Code
DB - Database

(or if you select ADMIN, you might only have these options as subtasks)
ID - Name
ACCT - Accounting
CLER - Clerical
TRATIM - Travel Time

Remember, the above are the admin screens where the associations would be set up. Next is the actual user interface on the timesheet screen.

Timesheet Display:
TimesheetId = 54, ending date = 03/05/05

(first task)
TimesheetTaskId = 67 (hidden field)
MyCompany, Inc. - Development - Code [billable checkbox] [hours sunday] [hours monday]... [hours saturday]

(second task)
TimesheetTaskId = 68 (hidden field)
MyCompany, Inc. - Administration [billable checkbox] [hours sunday] [hours monday]... [hours saturday]

Notice for the first task I list the customer name, task name, and subtask name but for the second task there is a customer and task name but no subtask name. You can add a timesheet task without a subtask as well and since a subtask can't exist from an administrative (see above) standpoint without a corresponding task, that's where things get hairy.

I think what I will do, considering I'm coming into this project and making modifications to an existing system, is append a system generated id field to the subtask table. That way I can refer to both Tasks and Subtasks independently in the timesheet_task table so as to avoid the hibernate mapping issue. If any of this makes sense, what are your thoughts on this revision? If this still doesn't jive I can always try to take screen shots and post them to make what I'm trying to say even more lucid.

I guess I would only make it a tad simpler. Change:

SUBTASK(
subtask_id [pk],
task_id [pk],
subtask_name,
...
)

to

SUBTASK(
subtask_id [pk],
task_id [fk],
subtask_name,
...
)

since in fact the task_id is in a different table. No need for an extra column if you have subtask_id as the sole id.

I discovered an issue between the design and requirements. The original spec was calling for a one-to-many relationship between task and subtask when in reality (and i was right on this) it was a many-to-many. I'm removing task_id from the subtask table and creating a join table between task and subtask for many-to-many relationship. Thank you very much for your time, mgreer. Without describing this all to a third party who knows how long it would have been before that disparity was discovered.

No trouble. Good luck!