2

Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships. How would i query these tables so that i can return all the related records between tables A, B, and C?

Is a setup like this a sign of bad design?

Edit: To list my specific issue, I have a table of electronic devices. Each device could have a feature such as wifi, and each feature could apply to that device. Now each feature could pose a security risk, and that same risk could apply to multiple features. So I have many devices, with many features, and many security risks. My goal is to get the results of a question in this form "For each device, show me each feature, and the security risks that it creates".

  • Do not think it is bad design. A books and authors are many to many so are books and keywords. What kind of data are you talking about? What is it exactly that you need for information from the query? – Marco Jun 22 '16 at 9:05
  • Typically, the better your normalization, the more common this structure is. – Steve Mangiameli Jun 22 '16 at 19:04
3

I think it is a sound design, based on what is called an associative entity. To query:

Select A.FieldList..., B.FieldList..., C.FieldList...
   From A
   Join AB On AB.AID = A.ID
   Join B  On B.ID   = AB.BID
   Join BC On BC.BID = B.ID
   Join C  On BC.CID = C.ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.