How to Use JSON Data Fields in MySQL Databases

In my article SQL vs NoSQL: The Differences I mentioned the line between SQL and NoSQL databases has become increasingly blurred with each camp adopting features from the other. MySQL 5.7 InnoDB databases and PostgreSQL 9.4 both directly support JSON document types in a single field. In this article we’ll examine MySQL’s JSON implementation in more detail.
(PostgreSQL supported JSON before version 9.4 and any database will accept JSON documents as a single string blob. However, both now directly support validated JSON data in real key/value pairs rather than a basic string.)
Just Because You Can Store JSON…
…it doesn’t follow you should.
Normalisation is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is clearly broken by storing multi-value JSON documents.
If you have clear relational data requirements, use appropriate single-value fields. JSON should be used sparingly as a last resort. JSON value fields cannot be indexed so avoid using it on columns which are updated or searched regularly. In addition, fewer client applications support JSON and the technology is newer and possibly less stable than other types.
That said, there are good JSON use-cases for sparsely-populated data or custom attributes.
Create a Table With a JSON Field
Consider a shop selling books. All books will have an ID, ISBN number, title, publisher, number of pages and other clear relational data. Presume we want to add any number of category tags to each book. We could achieve this in SQL using:
a tag table which stored each tag name against a unique ID, and
a tagmap table with many-to-many records mapping book IDs to tag IDs
It’ll work but it’s cumbersome and considerable effort for a minor feature. Therefore, we’ll define a tags JSON field in our MySQL database’s book table:

  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)

Continue reading %How to Use JSON Data Fields in MySQL Databases%

via Reme Le Hane

Starting a Business with Laravel Spark

I am really excited about Laravel Spark. By the time you read this, there will probably be a multitude of posts explaining how you can set it up. That’s not as interesting to me as the journey I’m about to take in creating an actual business with Spark!
The idea is simple. I have created a Pagekit module which you can use to back up and restore site data. The module makes it easy to store and download these backups, and restore them on different servers.
The trouble is, getting those backup files to the remote server takes time and a bit of hassle. I have often wanted a way to quickly and painlessly transfer this application state from one server to another, and make automated offsite backups. So I’m going to set that up for myself, and perhaps others will find it useful enough to pay for it.

Getting Started
I’m using Stripe, and intend to have a single plan with no trial. The setup for this is quite easy, but I’ve made a note of the plan ID. I’ll need that to set the plan up in Spark…

Next, I reset my secret and public Stripe keys and update to the latest API (through the same screen,
I forgot that the settings in .env do not automatically reload while the Laravel development server is running, so I was getting needlessly frustrated at the keys which wouldn’t seem to update.
Spark has a few expected registration/profile fields, but I want to add a few more. I would like to ask users if they would like automatic backups and I’d also like to collect their billing address, so I can show it on their invoice. First I’ll have to create a migration for the new field:
php artisan make:migration add_should_backup_field

To do this, we can add the column (making sure to remove it if the migrations are rolled back):
use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class AddShouldBackupField extends Migration

public function up()

Schema::table(“users”, function (Blueprint $table)

public function down()

Schema::table(“users”, function (Blueprint $table)

Continue reading %Starting a Business with Laravel Spark%

via Reme Le Hane

Icons and Teams

Say you’re working on a website that uses an icon system. Lots of people who work on the site interact with the icon system. Designers create new icons, they tweak existing ones, they have ideas on what they want the icons to do. Developers building out the pages of the site use the system.
Say you’re the front-end developer. You’re implementing this system. You’re the middle man. You’re the creator and consumer of this system.
What do you ask of …
Icons and Teams is a post from CSS-Tricks

via Reme Le Hane

Web Development Reading List #135: Boxy SVG, How To Keep Up, CSS Frameworks

After spring has started marvelously, this week brought us some snow again. But today, the sun is shining, it’s getting warmer, and nature is flourishing. Inspired by the fresh green of spring, I’d like to announce The Evergreen List.

This is a sub-part of my reading list, collecting important links that stay relevant over a longer time so that you can find them more easily. Give the page a try and if you have feedback, just email me.
The post Web Development Reading List #135: Boxy SVG, How To Keep Up, CSS Frameworks appeared first on Smashing Magazine.

via Reme Le Hane