sql - database Junction/association table with more data fields -

i not fan of having more 2 fields (eg: pk1 & pk2) in junction/association table. have situation, however, , not sure best approach resolve it.

table 1: contract (id, price, description)

table 2: person (id, firstname, lastname)

table 3 (junction/association) person_contract (person_id, contract_id)

so contract can have 1 ore more persons , person can have 1 or more contracts. hence need junction/association table; person_contract.

up here there no problem. persons per contract have sequence represents importance given contract. example: - person1, contract1, sequence = 1 - person2, contract1, sequence = 2 - person3, contract1, sequence = 3 - person1, contract2, sequence = 2 - person4, contract2, sequence = 1

the question save person sequence contract? adding sequence junction/association table doesn't right me. looking better approach if possible.

there's nothing wrong including other fields in kind of table. specific scenario you've described, can quite useful add rank column, , use sort people associated contract if want find important person. have used similar pattern before success.

thinking data modelling perspective, in entity-relationship terminology associative entity. wikipedia has matter:

• "[an associative entity] entity since may have own properties."
• "[an associative entity] may contain ... other information relationship."

so if data relationship modelled particular table, belongs in table.

i find helpful step out of database thinking kind of thing - rather seeing junction table, think group of people sign contract together. importance data questioning conceptually describes group of people in regards specific contract.

as far note in comment started thinking might problematic because of how hibernate generated entities based on tables, have seen questionable things created orm tools in both directions (i.e. poorly auto-generated databases , poorly auto-generated code). questioning output - have done - seems sensible. don't know whether these issues can worked around or whether it's inescapable cost of using auto-generation tools; worth new question aimed @ hibernate experts whether can accept kind of table , still auto-generate decent code.