What are the consequences of violating a one-to-many relationship & when to use M2M
Considering the amount of order records are many magnitudes larger then tags:
1. Iíve read a postsís comment where inserting order# 68 as pictured wouldnít cause any trouble but if I wanted to query orders by certain tags, a many-to-many is more appropriate/convenient/efficient since otherwise in a one-to-many every single order will have to be checked to know how its tags, is this true?
a. I also recall having read that many-to-many relationships cost the most in resources and donít perform well yet also read claims that the performance losses are negligible and not worth the risk and overhead of handling orphan records of many-to-many relationships, any comments?
b. Based on other readings I was convinced to model a many-to-many via making 2 one-to-many tables with a joint table of FKs, if there is no longer a many-to-many but 2 one-to-many tables are the aforementioned cons of many-to-many avoided?
2. In a similar question Iíve posted in another forum I was told that I couldnít even insert order# 68 and doing so would cause referential integrity issues, which nearly doesnít make sense, is this also true?
I want to rid myself of the conflicting and auto contradictory posts Iíve read.