PHP and true MVC structure with an advanced database structure
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. model.png
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.
Not sure what you mean about not enforcing referential integrity on the database side - it definitely belongs there.
Also - one tweak - why not just name the id field for each database table "id", as apposed to "userID, propertyID" etc? Just naming each id field "id" will make things simpler.
In terms of your other questions - personally, I think it sounds like you're gonna get way over your head very quickly. A better idea would be to get good with one of the better frameworks out there - something like either Zend or Symfony, and then work with them. The chances of you doing it better than one of those frameworks is basically zero...
*Edit: Also, why would a user have many passwords?