Building Netsoc Admin (#2) – Models and Relationships

Models aren’t just pretty runway strutters with capes draped over one shoulder and a small pomeranian in the other, they’re a vital part of the MVC methodology (it’s in the name after all). Models will be our main way of representing the underlying database and the relationships on top of that.

This is part 2 of a series on Building Netsoc Admin. If you haven’t already, you can read the previous parts over here.

Laravel ORM

Laravel provides a really nice interface for interacting with and exposing models (ooo la la). Laravel uses Eloquent ORM (Object-Relational Mapping) to handle its models and relationships. This will give us a nice way to add to, delete from, query and generally interact with the database. First thing’s first though, we need to architect our database and make sure we have something to interact with in the first place.

Database Migrations

All of our definitions and changes to the database will happen in sequential migrations. Working from scratch, on an empty development database, it may seem like a pain to use migrations but their main benefit comes as you build on top of your software in the future. Each migration script has a specific change and a method to entirely reverse it.

That way, our database changes are almost like version controlled commits. We can easily migrate (commit our change), rollback (reverse a change) and refresh (rollback all changes then migrate all of them again). In this blog post, we’re going to run a series of basic table creations to house all our data.

Making Migrations

To begin with, we’ll login to our vagrant box with vagrant ssh and then cd /var/www to get to the root of our project. To create a migration for a new table, we can run:

If we wanted to just make a change to an already existing table, we can leave out the --create flag and it won’t include the create table script in the new file.

For more information and details on how to run migrations, please refer to Laravel’s Documentation.

Users

Will create a new file titled [something]_create_users_table.php in the database/migrations directory with the following boilerplate code:

The function up() will be run during a migration and the function down() will be run during a rollback.

In this case, up() creates a new table with the following:

  • Auto-incrementing integer id that will be used as the primary key
  • The remember_token for remembering a user when they close their browser and come back later (it’s just like Facebook’s “remember me” checkbox when you’re logging in)
  • An updated_at and a created_at timestamp to log when every entry is created and updated

We’re going to put our own code in the up() function:

Settings

The settings table is pretty straightforward, much easier than above. As before, we start with our make:migrate command.

And the replace the up() method with the following:

MySQL Databases

Unfortunately, this ones a little trickier as we have to setup the relationship between the mysql_databases and the users table. Firstly, though, we create our file:

And we replace the up() method but, this time, we’re going to add a Foreign Key. If you’d like to learn more about Foreign Keys, check out W3Schools’ tutorial on the subject.

This creates a db-layer relationship between our databases and our users, I’ll explain a little more about relationships a little further down within the models so don’t worry too much about it right now.

Now, though, we have to consider what happens when we rollback a migration. For the first time, we’ve gotta replace the default down() function in order to drop the foreign key as well. It’s fairly straightforward, we simply put in the following:

We’re leveraging the table schema to drop the foreign key and then drop the table afterwards.

MySQL Users

Creating mysql_users is the same as above so I’ll simply include the three code segments.

Models

Models are bridges between the database layer and our code. They give us a way to extrapolate relationships and query data in a very easy, object-y way. It’s much more readable as well which is a bonus. To create a user, you create a user object and then the model creates all the relevant information in the database as well so, effectively, we’re dealing with two distinct layers in one singular method.

User Model

I’m going to start off with the “hardest” model simply because it’s the core of our application – the user. Since it’s a User model, we’ll have to use certain contracts to provide Authentication and PasswordResets.

A contract, if you’re unfamiliar, is an agreement a class has to fulfil to ensure it can perform the necessary functions required.

Below is the initial code for the User model we’ll place in app/user.php.

However, the key thing we’re missing here is the relationship between our User model and the MySQL models I’ll be setting up next. We’ll return to this.

MySQLDatabase and MySQLUser

Generally, models are fairly simple. Your database’s column names go in the $fillable array and anything sensitive goes in $hidden. There’s a template for every model. Have a look at the similarities between our code for aPP/Mysqldatabase.php and app/MysqLuser.php below:

MySQLDatabase.php

MySQLUser.php

Relationships

User <–> MySQLDatabase

Every user in our application will have a one-to-many relationship with MySQL databases. That is, every user may have 1 or more MySQL databases associated with them. As such, we’d like a way to use one to determine the other. We’ve already set up our foreign keys in the database, so all that’s left is representing the relationship in the Model.

As you can see from the diagram below, the user has a one-to-many relationship with the databases but the databases have a one-to-one relationship with the user. A user can have many databases but each database can only have one user.

Within the User model, we’ll add the following:

Now, whenever we want a user’s databases, we can use User::find(1)->databases and it’ll return an array of database objects associated with that user.

Conversely, to represent the other side of the relationship, we’ll add code to the MySQLDatabase model.

Then we can get any databases’ user with Mysqldatabase::find(1)->user.

User <–> MySQLUser

Finally, the netsoc account user and the MySQL user have a one-to-one relationship both ways which make it much easier to understand. This is because we will be using one MySQLUser to give someone database access via management GUI (such as PHPMyAdmin). It allows us to limit their permissions and monitor access.

user->dbrelation user->mysqluser

Leave a Reply

Your email address will not be published. Required fields are marked *