database Junction/association table with more data fields

0
votes

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.

1
sqldatabase-designmany-to-many
Do you have any reason for not wanting to put it in the junction table other than not liking how it looks? I certainly have tables models exactly in that way, with a third column labelled something like Rank to allow queries to pull many-to-many associations back in a certain order. I also have junction tables with other fields added. If that table leads to the correct cardinality and suits all of your needs, why not add the field there? What makes a junction table different to any other table in your mind? - Jo Douglass
en.wikipedia.org/wiki/Associative_entity Particularly note: "... it is also an entity since it may have its own properties." and "... may also contain ... other information about the relationship." - Jo Douglass
Thanks @jo-douglass. I ended up adding the field to the junction table as there is no other options. - zoro74
OK - I'll write up a proper answer in that case, might help someone else who's wondering about this in future. - Jo Douglass
The reason I didn't want to add it is because I use JBoss hibernate tools to generate entities from the DB tables. When the junction table had only 2 primary key fields, the generated code was very neat and clean. Once I added the sequence field to the table and regenerated code, the result was a bit of a mess. I thought maybe it's not the right way to do it. At the end, I generated the code without the field in there, then added it afterwards. Thanks again for your response. - zoro74

1 Answers

0
votes

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.