apache cayenne add weight on many to many relationship

Question

everyone.

I use apache cayenne on my project. got an issue can't work out.

I have a engineer table and a skill table, An engineer can have many skill and A skill can belong to many engineer, so it's a many to many relationship. I made a engineer-skill-relation table, then connected these three table with cayenne's "flatten relationship". everything's fine so far.

here is the problem, I got a requirement to represent "how well an engineer mastering a skill". It's like add a "weight" on the relationship, I think the best place will be engineer-skill-relation table, but how?

In the modeler? I didn't find anything help.

I don't know much about database design, so maybe there are some ways to solve this problem by adjust the table design?

any ideas are welcomed.

thanks.

Answer 1

In this case you need to forgo "flattenning" and make engineer-skill-relation into a regular entity. Db* side of the mapping should stay unchanged. On the Obj* end of the mapping instead of 2 entities with many-to-many, you will have 3 with a pair of one-to-many:

Engineer --> EngineerSkillRelation <-- Skill.