Hi guys
The MySql Mastery course is brilliant (what a great teacher!). I had completed all but the last two chapters. At that point, it was enough for me to confidently build my first large fully working and tested database project. The last two chapters are on security and making DBs high performance with Indexing which I will complete later.
Right now I want to alleviate refine my DB. See, in the course, Mosh mentioned two ways of forming primary keys in a derived associate/junction table derived from a many to many relationship. One way is to make the foreign keys a composite primary key which is the method Mosh demonstrates and which I adopted in my DB. The drawback of this method is that if the junction table is parent to another child table and that child table is parent to another Junction table and so on, then the composite primary key of the descendant junction tables gets quite large and “WHERE” queries would be very long. For instance I have a few generations level associated table that has eight foreign keys as a composite primary key!
Composite keys help me see the dependancies but queries to abstract data ends up being very long.
I’d like to try adopt the single primary key for associate/junction table method to see the difference in how the db handles queries. But Mosh didn’t show how to implement the other method of using a singular primary key for the junction table while retaining the foreign key relationships and ensuring uniqueness integrity. I can’t quite figure it out.
Can anyone advise?