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:

1. a tag table which stored each tag name against a unique ID, and
2. 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:

[code language=sql]
CREATE TABLE `book` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`tags` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
[/code]

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

by Craig Buckler via SitePoint

SpriteKit From Scratch: Constraints and Actions

plus mûrs

Arty minimal One Pager for French studio, plus mûrs. The Single Page site features a wild hover-sensitive project "index" that scrolls you up to relevant project sliders. There is also quite a unique horizontal accordion revealing an Instagram feed and more information about the studio.

by Rob Hope via One Page Love

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, http://ift.tt/1rnQsJq).

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 Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddShouldBackupField extends Migration
{
    public function up()
    {
        Schema::table("users", function (Blueprint $table) {
            $table->boolean("should_backup");
        });
    }

    public function down()
    {
        Schema::table("users", function (Blueprint $table) {
            $table->dropColumn("should_backup");
        });
    }
}

Continue reading %Starting a Business with Laravel Spark%

by Christopher Pitt via SitePoint

How to Authenticate Users With Twitter OAuth 2.0

Now You See Me 2

THE CLOSER YOU LOOK, THE LESS YOU SEE. Scroll to zoom out as you work your way through various scenes that showcase various illusions tied to the film.
by via Awwwards - Sites of the day

Google AMP: What Bloggers Need to Know

Have you heard of Google AMP? Want to know how it will impact your blog? To discover more about Google AMP and the future of blogging, I interview Leslie Samuel. More About This Show The Social Media Marketing podcast is an on-demand talk radio show from Social Media Examiner. It’s designed to help busy marketers [...]

This post Google AMP: What Bloggers Need to Know first appeared on .
- Your Guide to the Social Media Jungle

by Michael Stelzner via