SQL - n:m relations

Question

What about n:m relations? Is there any way in SQL to avoid creating two sub-tables (each having an 1:n relation) for such cases?

Answer 1

Unfortunately the only way to do many to many relations us to have an extra table. So say for example you were modeling classes at a university. You might have a table that models a student and another that models classes. Each student might belong to 0 or more classes and a class has one or more students. To model this relationship you'll need a third table that stores just the student's and the class' primary keys. This third table will have foreign key relationships to the other two tables.

I wish there was a better way to do this but SQL doesn't really provide an alternative.

Answer 2

@James Durand
Such a pity, but I already suspected that. I assume there might not even exist any alternative no matter whatsoever database-language one is using.. Nevertheless thanks for the help!

Answer 3

@James Durand, and et al,

For your student & classes example... consider that 3rd "relationship" table to be your "enrollments" table... often times enrollments are what the university gets paid for. The data model does not have to limit that table to only two columns. For a retailer, the sales transaction table is often similar (one entry may relate to customerID, orderID, productID, salespersonID, deliverySiteID, etc).

And yes, it is possible to record Many-to-Many relationships without a relationship table (or junction table), but you lose referential integrity and incur substantial performance penalties trying to reconstruct those relationships dynamically (at query time).

Answer 4

If you use a NoSQL database there are definitely options but this wouldn't be a good enough reason on its own to go that route.

Answer 5

My code