From database normality PoV, you should stick with your current structure
(approach 2). Complexity of SELECT query should belong to CakePHP engine,
not yours.
If you have defined the Model Association correctly, CakePHP will also
retrieve related tables, so you shouldn't worry about how to
I would definitely avoid the duplicate data scenario.
I have a users table (that has user_id, first_name, last_name...etc)
And a profile table (profile_id, user_id, country)
And a friends table which has (friend_id, user_id profile_id)
Why does the friends table have profile_id?
If
