I'm busy developing the first phase of a hotel booking system based on a pure PHP MVC structure. I tried getting acquainted with the libraries like Cake and CodeIgniter but I wasn't comfortable with how little control I had.
So I opted to build my own mini framework which has worked out quite well for me so far. Now, the database structure I'm using is rather complex. There are quite a few many-to-many relationships and one-to-many relationships. I opted to not enforce any referential integrity in the database itself as I believe that should be done at a programming level to avoid frustration later on - things don't always work as planned and I find it easier to figure out what's going on programmatically than in an SQL database with less then explanatory error messages
SO, to the crux - this is the structure I'm using.
The decision I'm struggling to make is where to place those arbitrary SQL statements to do with relationships between my different objects.
So, for example, I have two objects - users and properties. In this relationship, users are owners of properties that might house a room that will be rented out. In my Users object model (MVC) I have methods like 'insert', 'delete', 'update', 'getUser', 'userExists'. Similar methods exist in my properties model.
Each model extends a Database class that I created to leverage a connection to the database.
In this structure, where and how do I manage the relationships between these objects? The relationship is Properties_has_Users -> it's may-to-many. Where do I create the insert, update and delete methods for the relationship? In it's own object called "Properties_Users_Relationships? In the controller of one of the objects?
I'm new to these forums and in fact in my three years of development I've never posted on a forum - just read them so please let me know if there's something else you'd like me to post to reference or make it clearer.