Database Relationships and Migrations for NatureShares: article #5

Week 3 on my way to creating My first app in Laravel and React JS. Its part of my 10-week challenge. You can find my previous article on the database structure for my app here.

This week I dive deep into database relationships mapped in my Laravel application. Its a learn as you go approach.

Database relationships

NatureShares app allows a User linked to a Company to create Projects. Users can also NOT be linked to a Company. A Project has several Values (nature values) and a Value has several Projects. A User can have many nature Values and a Value can belong to several Users. Thus, hasMany, belongsTo and belongsToMany relationships have been mapped. Tables also allow JOINs: project_value and user_value. Naming conventions for pivot tables: singular and descending. I found this YouTube video very helpful :Pivot tables and many-to-many relationships in Laravel.

Foriegn keys constraints are also mapped. 1) A user_id is mapped as ‘owner_id’ in the companies table. This is helpful to authorise full CRUD control on projects. 2) Company_id is applied as a foreign key inthe projects table with CASCADE on delete.

🤔 I had a doubt on MySQL syntax while applying foreign key constraint: do I need to index the company_id in the projects table and also apply a foreign key constriant to it? I decided to give it the benefit of doubt by assigning both, but I am not sure if this is the right way to do it.

Project migration table schema looks like this:

Database Migrations

This is Laravel business-as-usual. Migrations, factories and seeders created the tables and put in dummy data. I used the Faker library features for the setup. 🤓

ProjectFactory.php looks like this:

That’s it in this section.