It's been a while (6 months) since I've used XDoclet and Hibernate, just to warn you.

I have a table in my database - position_skill

This table has a position_id column that refers to a position table. It also has a skill_id that refers to a skill table. Since both the position_id and skill_id foreign keys will have existing values, I should only need the following in my PositionSkill.hbm.xml file - right?

[code]<composite-id

For #1 I would say you don't even need to map the PositionSkill table if you can move the years property and make it purely a cross reference. If not, then to me it looks right the way it is (but composite-id's have been the bane of my existence so I could be dead wrong...).

For #2 you shouldn't need it to be composite because you can easily make a surrogate key and the positionId wouldn't be needed in the ID. Having said that, if you can't change your schema and you are going to have duplicate values of skill_name (so that you can not use it as a PK by itself even if it weren't questionable to use varchar for a PK...) then I suppose that what you have listed would be the way to go.

I can change the schema - in fact, we're trying to design it, with Hibernate in mind. We're building a job posting application that allows users to post new jobs into our webapp. The idea is that when they post a new position (job), they'll assign a number of skills. We have to allow them to free-form the skills, because there won't be a fixed set of skills. The second app we're building is a resume entry app, and users will enter skills too.

We're thinking of creating a position_skill table and a applicant_skill table. Put a composite-id on both, that contains a position_id foreign key. Does that make sense? Is there a better way?

Thanks,

Matt

I would recommend that you do whatever you can to avoid using composite ID's in your mapping (even if the table does have foreign key constraints) because it is much much easier to deal with cascading etc. if everything has it's own unique ID and generator strategy.

If what the users of the two apps are going to enter is going to be completely free-form (and you don't plan on forcing uniqueness of what they enter) then you can store each record with a unique ID and even though you will have a foreign key constraint to the position table in the database you don't need to put it as part of the mapping ID in Hibernate and get sucked into composite-id complexity.

Do you need to be able to easily match the position skills with the applicant skills or will it just be visual recognition on the part of the users?

Do you want to have, or want to avoid, bidirectional association between the various skills and the position?

Ideally, we would love to programmatically match the position_skills with the applicant_skills, but that is unlikely to happen. The person entering the position skills might have a totally different name for a skill vs. an applicant. For instance, a HR person might enter "Word Processing" and an applicant might enter "Microsoft Word" - close but tough to match programmatically. Maybe we could use regular expressions or something - who knows.

The idea is to make it easy for the HR person *and* the applicant to enter skills. Maybe we could lookup the existing list of skill names for each table and present them to users, but we'd have to enter a new record when they added them to a position to set the position_id in the position/applicant_skill table.

So if I understand you correctly, you're recommending I create two classes, PositionSkill and ApplicantSkill. Each class (table) is pretty much the same (with a generated skill_id), except that PositionSkill has a position_id column (Long) and ApplicantSkill has a applicant_id column (Long).

There is not mapping in the *Skill objects to their parents, but there is a mapping from Position and Applicant to their respective child tables? I'm assuming that creates a foreign_key contraint in the child tables?

Sounds simple enough (if it's possible).

Yes. And each would have a position_id column that would not be a part of the Hibernate id mapping (because, as you know, that would require a composite-id mapping). From what little I know about your project this sounds right to me.



Right, you would have foreign key constraints with the child tables in the database but so long as each child record has a unique key of it's own you don't need to get into composite-id in Hibernate which will cut down the amount of code you have to write (and make things easier to work with). And if you wanted to you could make it bidirectional with little effort.



That sounds like a good way to cut down on duplicate entries but you will need to use an x-ref I think to be able to reuse the skills on multiple positions.

You could conceiveably put all the skills in one table and make both groups of users pick from the same list but that might just complicate things also?

example mapping:


Something to that effect I think, there are lots of little things to tweak and work out (such as reusing records and having a x-ref and then using many-to-many instead of one-to-many etc.) and I may have not been detailed enough to be totally correct for it to run first try.

There are some full fledged examples in chapter 16 of the 2.1 documentation.