CodeIgniter Forums

Full Version: Junction tables place in CI MVC arhitecture
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
This is my first post so I would first like to say hey to the good community of CodeIgniter!

I have a question regarding model/controller design and some other stuff I don't understand completely.

I followed some tutorials, did some experimenting and finally decided to finish a project using CI. I have also been learning MySQL and OO PHP. Along the way I've shaken hands with Bootstrap (grid system, modals), JQuery plugins (multiselector, tinymce, datepicker) and server administration (I've made my own using RaspberryPi). This information is just to shed some light on the level of my experience (beginner to novice) Smile

So the project is actually a CMS of sorts to help out a local youth organization present themselves on the web. Right now I'm finishing the backend admin panel for adding content and some things didn't quite add up.

I have four tables and a junction table like so:
Users
Posts
Teams
Seasons
TeamsTo Seasons

There are 18 seasons so far and around 200 teams through out the organization history. I created a "edit"/"add new" view for each of the basic tables (Posts, Teams and Seasons) represented as a form. So there is many-to-many relationship between Seasons and Teams and the TeamsToSeasons junction table represents this relationship.

This is where the confusion kicks in. I've been using one core Model and two core Controllers (frontend/backend) as parents. My core model has all the basic CRUD functionality that worked perfectly with Posts and Teams tables, but I had to override all that functions for my Seasons model. Seasons model also takes TeamsToSeasons table as a field and I did not create a new model for junction table, but I already see some potential trouble on horizon (eg. if I would like to access junction table from team controller - just load the Season model in Team controller? I think my anal file organization mind set would suffer enormously this way)

And what about the relation I've set up in MySQL? I connected the foreign keys in TeamsToSeasons with their primary key counterparts in respected tables, but I don't see where this comes in handy. I could have as easily just set up a new junction table and not tell MySQL anything about relationship since it all takes place in my model logic inside the CI application. Am I missing something?

PS. I am fully aware there are tools that would make this project development faster, but the emphasis is on the learning process.

Thank you for your time and answers!

Best Regards,

Rok
In most database systems, foreign key constraints prevent you from violating those constraints when performing inserts/updates/deletes, and indexes are often automatically created to improve performance when using those keys. It's basically an extra layer of security for your data's referential integrity, and it allows someone with a database background (like a DBA, or another developer with DB experience) to understand the structure of your database without looking through your code.

With models, it's usually easiest to setup a model for each table in your environment, but reference/junction tables obviously don't gain significant benefits from this arrangement (though there are some benefits in certain environments to having a model mapped to such tables). In most cases, it would be better from your application's/controller's point of view to modify the reference table as a function of modifying the table(s) it references, rather than loading a model for the reference table and modifying it directly.

You really can approach this in three different ways:

1. You can create individual models for every table (including reference tables) which handle the low-level CRUD operations (most of which can be inherited from a base model). Then you can create higher-level models or libraries which model your business processes and use the lower-level models to access the database. For example, a higher-level model might load the Team_model, Season_model, and Team_to_season_model to create a new season and/or add a team to that season. The higher-level model might manage the relationships between the models and things like validation for those relationships, but would only really deal with the data in the database as arguments passed to/from the lower-level models. The higher-level model might take data from a controller and pass the relevant portion of that data to the Team_model, which inserts the team and returns the ID of that team. It could then query the Season_model to make sure the season information passed from the controller is accurate (or have the Season_model create a new season, if appropriate) and retrieve the ID for that season (whether a newly inserted season or a season which already exists in the database). Then the higher-level model could pass the two IDs to the Team_to_season_model to create the relationship between the new team and the season.

2. You can create a monolithic model of your business process which handles all of the data operations for a given process (or set of processes), accessing the underlying tables and the fields in those tables as needed, without the lower-level models. So, a single model might manage all operations involving teams, including creating the relationships between teams and seasons. On the other hand, this might mean that one model is used for creating/managing teams, another is used for managing the relationships between teams and seasons, and a third is used for creating/managing seasons.

3. You can manage the relationships between the tables in your controllers.

Generally, there are good and bad things about each of these methods.

For many CI developers, #3 is probably the most commonly-used option. If only one controller accesses the models in question and management of the relationships is fairly limited, it might even be a good option. It becomes a bad option as soon as you need to do anything with that data from another controller, but in most cases you can easily refactor the code from the controller into a library or model to get to #2 or #1. Another downside is that changing the underlying data structure may require changes in multiple locations, and it may not be easy to determine where those changes need to be made without a thorough understanding of the entire application (which may be trivial on some projects, impossible on others).

#2, or something similar, can be a good option under many conditions. If you have very well-defined business processes and the underlying data structure is unlikely to change, this can work very well. It can also be relatively easy to understand for new developers coming into (or taking over) a project. On the other hand, it may limit code reuse by creating a structure in which you have a model for each controller (or a set of models for each controller) and several models which access the same data. If the underlying data structure changes in some way, as with #3, you may have to change code in multiple locations to reflect those changes.

#1 gives you a lot of flexibility, with the cost of appearing more complex. In most cases, actually using the models and creating new controllers and models to access the same data is usually easier than in #2 and/or #3. For a new developer coming into (or taking over) a project built like this, it may initially appear to be unnecessary complexity, but, perhaps with the help of some documentation and examples, most developers will begin to understand the benefits as they develop new functionality in the application. Additionally, with some forethought in designing the lower-level models, sometimes drastic changes can be made to the underlying data structure without ever having to change any of the higher level models.

Besides the added number of files required (in some cases) and the initial difficulty in understanding the interactions between classes in the application, many developers avoid #1 because they believe that loading more models and adding more layers between their controller(s) and the data will ultimately lead to performance issues. In practice, the database actions themselves have a much larger impact on performance than the number of files you use to determine those actions. It's much more efficient to take advantage of the layered approach for the majority of use cases and optimize the performance only in cases where it is found wanting.
Thank you so much for taking the time and explaining the different approaches. I will start working on the first approach; its structure makes most sense.

Cheers!