CODEWITHSUNDEEP
sqldatabase-designmany-to-many
zoro74
zoro74

Reputation: 171

database Junction/association table with more data fields

I am not a fan of having more than two fields (eg: pk1 & pk2) in a junction/association table. I do have a situation, however, and not sure what would be the best approach to 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 a contract can have one ore more persons and a person can have one or more contracts. Hence the need for a junction/association table; person_contract.

Up to here there is no problem. Now persons per contract have a sequence to represents their importance to a given contract. For example: - person1, contract1, sequence = 1 - person2, contract1, sequence = 2 - person3, contract1, sequence = 3 - person1, contract2, sequence = 2 - person4, contract2, sequence = 1

The question is where to save the person sequence for a contract? Adding the sequence to the junction/association table doesn't look right to me. I am looking for a better approach if possible.

Upvotes: 1

Views: 429

Answers (1)

Jo Douglass
Jo Douglass

Reputation: 2085

There's nothing wrong with including other fields in this kind of table. For the specific scenario you've described, it can actually be quite useful as you could add a Rank column, and then use that to sort the people associated with a contract if you want to find the most important person. I have used a very similar pattern before with success.

Thinking about this from a data modelling perspective, in entity-relationship terminology this is an associative entity. Wikipedia has this to say about the matter:

  • "[An associative entity] is also an entity since it may have its own properties."
  • "[An associative entity] may also contain ... other information about the relationship."

So if some data is about the relationship modelled by a particular table, then it belongs in that table.

I often find it helpful to step out of database thinking with this kind of thing - rather than seeing it as a junction table, you could think about it as a group of people who sign a contract together. The importance data you were questioning conceptually describes something about that group of people in regards to a specific contract.

As far as your note in the comment that you started thinking this might be problematic because of how Hibernate generated entities based on the tables, I have seen some very questionable things created by ORM tools in both directions (i.e. poorly auto-generated databases and poorly auto-generated code). Questioning the output - as you have done - seems sensible. I don't know whether these issues are something that can be worked around or whether it's the inescapable cost of using auto-generation tools; it could be worth a new question aimed at Hibernate experts as to whether you can get it to accept this kind of table and still auto-generate decent code.

Upvotes: 1

Related Questions