table composite primary key and one to many relationship

[emilyjac]

Hi.

I have a junction table with a composite primary key (ie. two field are used to define the primary key). I need link a third table to the junction table in a one to many relationship. In the attached photo relation_probleme.jpg, the table without any relations should be on the "many" side and the junction table on the "one" side.

The idea is that there are a many "elements" elements linked to many "inspections" and vica-versa. for each pair element-inspection i want to attribute a "condition", in turn this one "conditon" can be linked to many "recommandations".

I have considered using a single field primary key in the junction table called id_cond, but I don't want to do it like this because i don't want to the possibility of an element being attributed multiple conditions for the same inspection.

So how do I do it?

[NeoPa]

You can link [tbl_cond_recommand] by adding both fields of the composite PK of [tbl_condition_ele] to it.

However, you can also assign a single-field, unique PK to [tbl_condition_ele] if you choose (and there are reasons why it would be advisable). Having a PK doesn't stop any other indices from being set up as mandatorily unique.

[emilyjac]

Hello. Thank you for the info about being able to have unique indices that aren't primary keys. I used it in a slightly different way than you suggested. I made a triple field primary key and made one of those fields unique (Noname.jpg). Does anyone have any feed-back about which of the solutions presented is best, and why?

[NeoPa]

That's not a good idea Emily. Never make an index more than it needs to be. [id_cond] can (should) be the PK and a compound index including [id_element] & [id_inspec] can be set up as unique. What more you may need/require is down to what you require the design to do for you.