Staying Motivated as an Entrepreneur – Top Tips from Some of the World’s Most Successful Entrepreneurs

Staying motivated is one of the most difficult things for entrepreneurs. It is the lack of motivation that causes many entrepreneurs to quit.

Below are some tips on how some of the world’s successful entrepreneurs stayed motivated despite the ups and downs in the business.

Joe Martin, President of Merchandize Liquidators, LLC

Joe Martin, an Israeli-born, founded Merchandize Liquidators while pursuing a Business Administration degree at Florida International University. Hardworking and persistent to finish college, he opened Merchandise Liquidators to pay his way through college. Although faced with different challenges during the start of his business such as time and money for capital, it didn’t stop him from reaching his goal.

He was an international student during that period, which means he had to stay as a full-time student for legal reasons. The main issue he faced regarding his business was to convince major chain stores that he could get their closeouts on a consistent basis regardless of the lack of capital to buy goods and space to place it.

According to Joe Martin, socializing with the most successful people is the key to staying motivated. He says that when you socialize with people who are at a higher level of success than you, you get a mental push that will help you break the barriers that are stopping you from making millions.

See Also: 7 Power Habits of Great Leaders, Business Icons and Inspirational Achievers

Chase Jarvis, CEO at CreativeLive

chase jarvis

Chase Jarvis, along with Craig Swanson, founded CreativeLive. Chase, a leader and a renowned creative himself, dedicated his company to providing an online space for visionary instructors to connect with creative professionals. He was not just the present CEO at CeativeLive, but also a director, an award winning photographer, and media maven. Furthermore, he created campaigns for Starbucks, RedBull, Nike, Apple, and other companies. He is also known for creating Best Camera, the world’s first photo application that lets the user share images to social networks.

Chase Jarvis first took the spotlight when he became the world’s best photographer at a young age. He went ahead to create CreativeLive which is the world’s biggest live-streaming education company.

Chase says that much of his success and motivation was from following his passions. He says that you should go after something that is dear to you and not random market opportunities. If you go for random opportunities, you will only be doing it for the dollars and thus you will not have the passion to stay with it.

Juanita Hines, Regional Consulting

juanita hines

Juanita Hines, passionate about helping people succeed, founded Regional Consulting. Because of her ability to help craft perfect resumes for her clients, she was known to be the “Resume Whisperer.” Her passion for helping has led her to start her company dedicated to professional empowerment by providing interview coaching and training in communities where no such opportunities were previously available.

Juanita also does a lot of speaking engagements at youth camps and schools. She also works with other non-profit organizations in encouraging students to start planning for their futures and assist professionals with insights on how to heighten their careers.

Juanita says that you should surround yourself with people who are striving to pursue their own success. This will encourage you to succeed in your own life as well.

See Also: 5 Women Entrepreneurs Who Failed Before Becoming Millionaires 

Tim Ferriss, best-selling author and CreativeLive instructor

tim ferris

Tim Ferriss, considered as one of “Most Innovative Business People, is also an author. He wrote “The 4-Hour Workweek, the 4-Hour Body, and the 4-Hour Chef”, considered as Wall Street Journal and #1 New York Times bestsellers. Furthermore, he is an investor and consultant in Twitter, Facebook, Shopify, Uber, Duolingo and much more. His podcast was also recognized as the “Best of 2015” by Apple and the #1 business podcast on iTunes.

Tim’s key to success and staying motivated is to choose your friends wisely. He says that the best advice he ever received and still keeps him going is that you become an average of the 5 people who you associate with most.

Will Curran, President of Endless Entertainment

will curran

Will Curran, who was only 17 during that time, started his interest in DJing. He found a need missing in the entertaining industry and went head-first into satisfying such need.

Due to his passion for putting on events, each mistake he made became building blocks to grow his company further. One of his discoveries during the journey towards his goal was how customers would start coming to him if he provided excellent customer service after which everything would fall into place.

Will says that you succeed by fighting the small battles. He says he finds joy in overcoming obstacles and celebrating even small wins. He rewards himself after all the jobs are well done. According to Will, you can get overwhelmed if you focus too much on the bigger picture.

Justin Palmer, Founder & CEO of MedSaverCard

Justin gets his motivation by thinking about his future freedom. He says that entrepreneurship requires a lot of hard work but if you concentrate on your business now, it will give you the freedom and resources for the years to come.

 

The post Staying Motivated as an Entrepreneur – Top Tips from Some of the World’s Most Successful Entrepreneurs appeared first on Dumb Little Man.


via Dumb Little Man – Tips for Life
Staying Motivated as an Entrepreneur – Top Tips from Some of the World’s Most Successful Entrepreneurs

The Hard Drives Most (and Least) Likely to Fail, According to Backblaze

Every year, backup service Backblaze takes a look at failure rates amongst the hard drives in its data centers. This year’s no different, and they’ve got all kinds of stats for all kinds of drive.

By the end of 2016, Backblaze cataloged 73,653 hard drives. Of those drives, three models, a 4TB Toshiba model, 8TB Toshiba model, and 8TB Seagate model were the only ones that recorded no drive failures, though that 8TB drive came out in November so it doesn’t really qualify. Perhaps most importantly though, the overall number of failures for the year is only 1.95%, down from 2015’s 2.47% and 2014’s 6.39%.

As for failures by vendor for the year, WDC tops the list, with a 3.88% failure rate, though when it comes to volume, Seagate’s 2.65% failure rate is notable because of the number of drives in the test (45,531 as opposed to WDC’s 1,626). How Backblaze account for failures, drive counts, and drive days is all documented over on their site if you’re curious.

Perhaps even more interestingly, they’ve also included statistics ranging back all the way to 2013, where one particular Seagate model (ST1500DL003) had an astounding failure rate of 90.92%.

Of course, all this data is limited to the data Backblaze collects, which is limited to the drives in their data centers. This doesn’t necessarily resemble the typical consumer experience since you’re probably not putting your hard drive through nearly the use they are. Regardless, it’s a fascinating look and a good starting point if you’re shopping for a new hard drive or a little worried about one you’re using.

Backblaze Hard Drive Stats for 2016 | Backblaze


via Lifehacker
The Hard Drives Most (and Least) Likely to Fail, According to Backblaze

It’s Finally Easy to Watch Other Space, the Best Scifi Comedy You’ve Never Seen

Do you remember Paul Feig’s scifi comedy Other Space? You’ll be forgiven for saying no. The 2015 series premiered on Yahoo Screen and felt like it promptly vanished off the face of the Earth because of it. Which is a damn shame, because it’s bloody brilliant—and now, it’s actually easy to watch in its entirety (for free!), which is something you totally should do.

Other Space followed the profoundly dysfunctional crew of the UMP Cruiser, after they get stranded in another universe during their first mission. The cast was stacked with great comedians including MST3K’s Joel Hodgson, a pre-Ghostbusters reboot Neil Casey, and Eugene Cordero, who’s basically been in everything. There were only eight episodes in the first season, but Screen’s geolocking meant that not only did Other Space not really have much of an audience, the chance for more was all but gone when Screen itself shuttered early last year—and with it, we lost a smart, incredibly funny scifi sitcom.

The rights to the show have reverted back to Feig, who understandably feels like Other Space got a rough deal. But speaking to Splitsider recently, Feig said he wanted to turn that around by attempting to drum up some enthusiasm for the show—something he and former showrunner Owen Ellickson could then use to pitch another season of Other Space to networks. Such enthusiasm should be easy to find now, as Feig has made the entire first season is now available to watch, from anywhere in the world, on the show’s own Tumblr. For free!

So when you’ve got a moment, check it out—you won’t regret it, and hopefully it leads to Other Space finding new life on TV.

[Splitsider via AV Club]

via Gizmodo
It’s Finally Easy to Watch Other Space, the Best Scifi Comedy You’ve Never Seen

MySQL Procedure Analyse Use

Sometimes we are not sure if the database design we have created and our site has been using is correct and optimised. We all do have this feeling at times don’t we 🙂

Well, MySQL provides with PROCEDURE ANALYSE() to help us detect inconsistencies in our database design by suggesting for an optimal datatype and data length for columns.

The syntax for using PROCEDURE ANALYSE() is as below:

SELECT ... FROM ... WHERE ... PROCEDURE
ANALYSE([max_elements,[max_memory]])

Ref: http://ift.tt/2kOxxvQ

We will create a sample database for our case here and try out some things.

Lets create a database called analyse_db.

mysql> CREATE DATABASE `analyse_db`
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then lets create a table called employee where we can keep our data.

mysql> CREATE TABLE `analyse_db`.`employee` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`dept_id` INT NULL DEFAULT NULL,
`country` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;

Lets insert some data into it, so that finally we have some records in it as displayed below:

mysql> SELECT * FROM `employee`;

Let us now run PROCEDURE ANALYSE() on the table.

mysql> SELECT * FROM `employee` PROCEDURE ANALYSE();


So from the analysis result we can take away below points:

First row output

1. From the first row output above, employee.id column seems to be fine, but the output suggests that we have enum as datatype for it. The suggestion makes sense given the less number of records we have in our table right now but in the long run our table would be much larger and each row value would be unique and enum would not suit our purpose. So we can neglect this suggestion.

Second row output

2. From the second row output above, employee.name has a min and max length of 8 and 13, average length of 10 and we have assigned a max varchar length of 100 while creating the table. This seems like a wrong decision on our part to assign it 100, when the range of length for values for this column is in between 8 and 13, but taking into consideration the very less data and our future estimate that there will be longer names with some even having middle names, our column design is fine. Also taking into consideration point 1 above, enum type can similarly be neglected for this column too.

Third row output

3. From the third row output above, employee.dept_id has repeated values of same departments which if are not mapped to another department table and do not have probability to change then they can be added as a enum type, but since there may be a probability that a new department can be added in future, our consideration for the data type is fine.

In addition to this, the result also shows that we have allowed for NULL in our schema while there are no NULL values in any of the values for the column, so we can mark it as NOT NULL, which makes SQL operations faster, provided we also have in our requirement that every employee should have a department value assigned.

Excerpt from the MySQL site manual page related to table column optimisation:

"– Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

– Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column."

Ref: http://ift.tt/1r4rnuX

Fourth row output

4. From the fourth row output above, our country list consists of only four set of values for this example but in a real world example this may vary with an increased constant list which is very rarely modified. So in this case we can change this column to enum with the list of countries, since this will help us save some storage space and will provide us an additional constraint on the values stored for country.

Note: Even phpMyAdmin has an interface to run this check and access this info. In the table structure page, you will find a "Propose table structure" link which does this action for you as shown below:

Propose table structure link.
Propose table structure action result.

So to conclude PROCEDURE ANALYSE() provides some very important insight into schema design for an optimal data type like column length, type and NULL value usage, which we can consider, but not all of it needs to be taken into account considering application requirement, future data possibilities and usage 🙂
via Planet MySQL
MySQL Procedure Analyse Use

If You Call This a ‘Muslim Ban,’ You Are a Shameless Liar

I’m not going to bother with an in depth breakdown of Trump’s Executive Order on immigration. Plenty of those have already been written, and a few of them are even accurate (examples: here and here). I have only a few random points that need to be emphasized:

1. EVERYONE CALM DOWN.

I wasn’t able to pay very close attention to the news this weekend, so I witnessed the hysterical meltdown over the Executive Order before I’d had a chance to read the actual text (a step that 98 percent of the protesters have clearly not taken). From the way the Left went into full meltdown mode, you’d think that Trump took some kind of extreme, incredible, unthinkable step. That is, you might come to that conclusion if you hadn’t noticed that the Left is now in a state of perpetual nuclear meltdown. The Fukushima meltdown went on for three days; the Left’s own nuclear meltdown is sure to outlast that by a factor of a thousand.

Trump’s action here is certainly more than Obama ever did or ever would do, but it’s not entirely unprecedented even by Obama’s standards. As has been pointed out many times since Friday, Obama put a temporary hold on the Iraqi refugee program for 6 months back in 2011. If you happened to be in an airport after Obama signed that order, you may have noticed the complete lack of protesting going on. On second thought, you probably would not have noticed because, back in those ancient times, you never would have expected to see a national panic over a reasonable measure meant to ensure that terrorists aren’t entering the country. But those were also the days when Obama could assassinate US citizens without the Left uttering a word or protest. They were more easy going back then, it seems. I wonder why?

Anyway, yes, Trump’s order goes beyond Obama’s. Trump is putting a hold on the Syrian refugee program until the process can be revamped and security measures are put in place. He’s also suspending all refugee admissions for just 4 months. And he is temporarily preventing the citizens of 7 terrorists hot spots from entering the United States. Those hot spots are: Iraq, Syria, Iran, Libya, Somalia, Sudan, and Yemen. It should be noted that Trump did not single out those countries by name in his Executive Order. He simply adopted the Obama Administration’s list of “countries of concern.” It was Obama who highlighted them initially. Trump just took the next logical step.

If these measures seem radical, they only seem radical to us because we’d grown accustomed to a president who did very little to protect national security and sovereignty. Indeed, we’d grown accustomed to a president who, infamously, couldn’t even bring himself to verbally acknowledge Islamic terrorism. Compared to that, yes, what Trump has done here is absolutely shocking. Looked at objectively, however, it’s honestly not that radical. It’s not a big deal at all, really. It’s a sensible first step towards ensuring that American citizens are better protected from the violence and chaos overseas.

What would these protesters have us do, anyway? Somalia, Syria, Libya — these are failed states. They’re literal breeding grounds for terrorism. They’re like an assembly line for mass murdering zealots. These are some of the most dangerous places on Earth. Nobody is saying that we should never again admit anyone from this region of the world, but it seems enormously sensible to take a brief and temporary pause to assess how we admit them.

Should we not even do that much? Really?

Continue Reading

The post If You Call This a ‘Muslim Ban,’ You Are a Shameless Liar appeared first on The Matt Walsh Blog.

via The Matt Walsh Blog
If You Call This a ‘Muslim Ban,’ You Are a Shameless Liar

PHP and MySQL Basics IV — SQL Injection and Prepared Statements

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid.

Rule 1: Never Trust User Supplied

The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id. But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what.

The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.

if is_int($customer_id) {
//Do all the stuff we want to do if we have a integer
//submitted for a customer_id
} else {
echo "Hey! I want an INTEGER for a customer identification number!";
}

Even more dastardly and from the PHP Manual where the dastardly injector resets all the passwords:


<?php
But a malicious user subits the value ' or uid like'%admin% to $uid to change the admin's password, or simply
sets $pwd to hehehe', trusted=100, admin='yes to gain more privileges. Then, the query will be twisted:
>?php

// $uid: ' or uid like '%admin%
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%';"
// $pwd: hehehe', trusted=100, admin='yes
$query = "UPDATE usertable SET pwd='hehehe', trusted=100, admin='yes' WHERE
...;";?>

Paranoid yet? This is another case checking the values for reasonableness can save grief. Is the uid an integer, is that integer in a proper range for uids? If too low or too high, you need to suspect someone is doing something bad.

On the database side

One thing I have recommended and implemented for years is separate users for SELECTs and INSERT/UPDATE/DELETEs. It is too easy to use one connection string over and over. But if you are in a situation where you need to take ‘loose’ information from an user and use that information against you data base, you need to split the queries. Set up a cust_read account on the MySQL server that can read customer data ONLY. The a corresponding cust_mod for all the other queries. This way you are assured that they can not delete or update records with that account if things get compromised. Also do not user superuser accounts like root for production database interactions — save these accounts for maintenance functions.

Bound variables

The MySQL server supports using anonymous, positional placeholder with ?. Again from the PHP Manual:


/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Or as used in a SELECT


/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
} else {
echo "Got it!!!\n";
}

Prepared Statements – Help But Not A Cure

Prepared statements make it nearly impossible to cram lots of data into one variable. That was nearly impossible.

Take the extra time to make sure that integers are really integers and that they are in the correct range of numbers. If you are looking for a string of up to say 30 characters that you do something when the length is over run. MySQL used to get a lot of flack with people running servers in less that strict mode and having excess data truncated with only a warning generated. Now sometimes that data is valuable. But if your corporate standard is to store emails in 45 characters and the user is trying to store 50, you need to programmatically warn those with long emails that their data is too long (and the Ops folks that the email to the account if going to bounce (if you do accept that truncated email)).

RTFM

The PHP Manual’s section on SQL Injection is a must read. Do use separate MySQL accounts for SELECTs and INSERT/UPDATE/DELETEs. Keep asking yourself how to keep from exposing more data than the absolute minimum needed. Do not use SELECT * FROM foo but instead explicitly name the columns in your select statement; If something goes wrong you are not exposing data column that may have private information. And be paranoid — keep asking if there is something else you can do to protect the data.
via Planet MySQL
PHP and MySQL Basics IV — SQL Injection and Prepared Statements

MySQL Sharding Models for SaaS Applications

MySQL Sharding Models

MySQL Sharding ModelsIn this blog post, I’ll discuss MySQL sharding models, and how they apply to SaaS application environments.

MySQL is one of the most popular database technologies used to build many modern SaaS applications, ranging from simple productivity tools to business-critical applications for the financial and healthcare industries.

Pretty much any large scale SaaS application powered by MySQL uses sharding to scale. In this blog post, we will discuss sharding choices as they apply to these kinds of applications.

In MySQL, unlike in some more modern technologies such as MongoDB, there is no standard sharding implementation that the vast majority of applications use. In fact, if anything “no standard” is the standard. The common practice is to roll your own sharding framework, as famous MySQL deployments such as Facebook and Twitter have done. MySQL Cluster – the MySQL software that has built-in Automatic Sharding functionality – is rarely deployed (for a variety of reasons). MySQL Fabric, which has been the official sharding framework, has no traction either.

When sharding today, you have a choice of rolling your own system from scratch, using comprehensive sharding platform such as Vitess or using a proxy solution to assist you with sharding. For proxy solutions, MySQL Router is the official solution. But in reality, third party solutions such as open source ProxySQL, commercial ScaleArc and semi-commercial (BSL)  MariaDB MaxScale are widely used. Keep in mind, however, that traffic routing is only one of the problems that exist in large scale sharding implementations.

Beneath all these “front end” choices for sharding on the application database connection framework or database proxy, there are some lower level decisions that you’ve got to make. Namely, around how your data is going to be led out and organized on the MySQL nodes.

When it comes to SaaS applications, at least one answer is simple. It typically makes sense to shard your data by “customer” or “organization” using some sort of mapping tables. In the vast majority of cases, single node (or replicated cluster) should be powerful enough to handle all the data and load coming from each customer.

What Should I Ask Myself Now?

The next set questions you should ask yourself are around your SaaS applications:

  • How much revenue per customer are you generating?
  • Do your customers (or regulations) require data segregation?
  • Are all the customers about the same, or are there outliers?
  • Are all your customers running the same database schema?

I address the answers in the sections below.

How Much Revenue?

How much revenue per customer you’re generating is an important number. It defines how much infrastructure costs per customer you can afford. In the case of “freemium” models, and customers generating less than $1 a month an average, you might need to ensure low overhead per customer (even if you have to compromise on customer isolation).

How much revenue per customer you’re generating is an important number. It defines how much infrastructure costs per customer you can afford. In the case of “freemium” models, and customers generating less than $1 a month an average, you might need to ensure low overhead per customer (even if you have to compromise on customer isolation).

Typically with low revenue customers, you have to co-locate the data inside the same MySQL instance (potentially even same tables). In the case of high revenue customers, isolation in separate MySQL instances (or even containers or virtualized OS instances) might be possible.

Data Segregation?

Isolation is another important area of consideration. Some enterprise customers might require that their data is physically separate from others. There could also be government regulations in play that require customer data to be stored in a specific physical location. If this is the case, you’re looking at completely dedicated customer environments. Or at the very least, separate database instances (which come with additional costs).

Customer Types?

Customer size and requirements are also important. A system designed to handle all customers of approximately the same scale (for example, personal accounting) is going to be different than if you are in the business of blog hosting. Some blogs might be 10,000 times more popular than the average.

Same Database Schema?

Finally, there is a there is the big question of whether all your customers are running the same database schema and same software version. If you want to support different software versions (if your customers require a negotiated maintenance window for software upgrades, for example) or different database schemas (if the schema is dependent on the custom functionality and modules customers might use, for example), keeping such customers in different MySQL schemas make sense.

Sharding Models

This gets us to the following sharding isolation models, ranging from lowest to highest:

  • Customers Share Schemas. This is the best choice when you have very large numbers of low-revenue customers. In this case, you would map multiple customers to the same set of tables, and include something like a customer_id field in them to filter customer data. This approach minimizes customer overhead and reduces customer isolation. It’s harder to backup/restore data for individual customers, and it is easier to introduce coding mistakes that can access other customers data. This method does not mean there is only one schema, but that there is a one-to-many relationship between schemas and customers.  For example, you might have 100 schema’s per MySQL instance, each handling 1000 to 10000 customers (depending on the application). Note that with a well-designed sharding implementation, you should be able to map customers individually to schemas. This allows you to have key customer data stored in dedicated schemas, or even on dedicated nodes.
  • Schema per Customer. This is probably the most common sharding approach in MySQL powered SaaS applications. Especially ones that have substantial revenue ($10+ per month / per customer). In this model, each customer’s data is stored in its own schema (database). This makes it very easy to backup/restore individual customers. It allows customers to have different schemas (i.e., add custom tables). It also allows them to run different versions of the application if desired. This approach allows the application server to use different MySQL users connecting on behalf of different customers, which adds an extra level of protection from accidental (or intentional) access of data that belongs to different customers. The schema per customer approach also makes it easier to move the shards around, and limits maintenance impact. The downside of this approach is higher overhead. It also results in a large number of tables per instance, and potentially larger numbers of files (which can be hard to manage).
  • Database Instance per Customer. You achieve even better isolation by having a MySQL instance per customer. This approach, however, increases overhead even further. The recent rise of light virtualization technologies and containers has reduced its usage.
  • OS Instance/Container per Customer. This approach allows you to improve isolation even further. It can be used for any customer, but can also be applied to selected customers in a model that uses Schema per Customer model for a majority of them.  Dedicated OS Instance, with improved isolation and better performance SLAs, might be a feature of some premium customer tiers. This method not only allows better isolation, but it also let’s you handle outliers better. You might chose to run a majority of your customers on the hardware (or cloud instance) that has best price/performance numbers, and also place some of the larger customers on the highest performance nodes.
  • Environment per customer. Finally, if you take this all the way you can build completely separate environments for customers. This includes databases, application servers and other required components. This is especially useful if you need to deploy the application close to the customer – which includes the appliance model, or deployment in the customer’s data center or cloud provider. This also allows you to accommodate customers if their data must be stored in a specific location. This is often due to government regulations. It is worth noting that many SaaS applications, even if they do not quite have one environment per customer, have multiple independent environments. These are often hosted in different locations or availability zones. Such setups allow you to reduce the impact of large-scale failures to only a portion of your customers. This avoids overloading your customer service group and allowing the operational organization to focus on repairing smaller environments.

The farther you go down this route – from the shared schema to an environment per customer – the more important is to have a high level of automation. With a shared schema, you often can get by with little automation (and some environments manually set up) and all the schema’s pre-created. If customer sign up requires setting up dedicated database instance or the whole environment, manual implementation doesn’t scale. For this type of setup, you need state-of-the-art automation and orchestration.

Conclusion

I hope this helps you to understand your options for MySQL sharding models. Each of the different sharding models for SaaS applications powered by MySQL have benefits and drawbacks. As you can see, many of these approaches require you to work with a large number of tables in the MySQL – this will be the topic of one of my next posts!

via MySQL Performance Blog
MySQL Sharding Models for SaaS Applications

What Chicken Breed To Buy In 2017

With so many chicken breeds on the market, let’s take a few minutes to talk about traits you want before you buy those spring chicks.

Usually starting in late January and going through March, the local farm supply stores will get their spring chicks in.  Please avoid the big-box-marts and buy from the local family owned stores.

Questions To Ask

Have a few questions ready before you go to the store.

Do you want brown or white eggs?  Ideal 236 and Leghorns lay white eggs, while most other breeds lay brown eggs.

Do you live in a cold or hot climate?  Certain chicken breeds deal with hot and cold better than others.

Will the chickens be confined to an enclosed run, have a chicken yard or free range?  Ask which breeds deal with various types of enclosures.

Do you want hens to sit on eggs to hatch out chicks?  This is called going broody or sitting.  If you do not have a rooster, hens sitting on eggs is a waste of time.  While sitting on eggs, hens stop laying.

Breeds that go broody include:

  • Buff Orpington
  • Australorp
  • Dominique, also known as Dominicker
  • Delaware
  • Silkies and Bantam, which are a small breed of chicken.

Egg Production

If all you want are chickens for egg production:

  • White Leghorn
  • Ideal 236
  • Rhode Island Red
  • Barred Rock
  • Silver Laced Wyandotte
  • Production Red

Those chicken breeds rarely go broody. Meaning, they rarely sit on eggs to hatch them out.

Butchering

All the chickens mentioned so far are slow growing heritage breeds.  They will not be full grown until they are around one year old.

If you want a meat chicken that you plan on butchering and want it to grow fast, ask the local farm supply store about “broilers.”  Most broilers are ready to butcher by the time they are two months old.

Broilers are not the kind of chicken you keep around for years.  They are usually heat sensitive and will die when the summer heat kicks in.  Get the broiler, feed them a high protein feed and butcher them when they are around two months old.

Dual Purpose

Most chickens fall under the dual purpose category.  This means they are good egg layers and are good sized for butchering.  If you do not want to butcher a bunch of broilers at one time, then get dual purpose.

Examples of dual purpose breeds:

  • Buff Orpington
  • Jersey Giant
  • New Hampshire Red
  • Barred Rock
  • Rhode Island Red
  • Australorp
  • Wyandottes

Number Of Chicken Breeds

The breeds mentioned so far, is a very small sampling of the number of chicken breeds on the market.  Every spring I learn something new.

Each breed has their own traits, and each chicken has their own personality.  Just because breeds are supposed to act a certain way, does not mean all chickens of that breed are that way.

The post What Chicken Breed To Buy In 2017 appeared first on AllOutdoor.com.

via All Outdoor
What Chicken Breed To Buy In 2017

How Chains are Made

How Chains are Made

Link

Cool factory footage from an old episode of How It’s Made, showing off the process of creating chain from long spools of wire. It’s neat to see how the wire is cut, bent, linked together, and hardened, but the entire time, all we could think of was Rick & Morty anyhow.

The post How Chains are Made appeared first on The Awesomer.

via The Awesomer
How Chains are Made

Working with JSON in MySQL

JFti976TQaVbpi0ZoYIy_scotch-featured-image-guidelines.jpg

SQL databases tend to be rigid.

If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it’s called structured query language.

On the other side of the horizon, we have the NoSQL databases, also called schema-less databases that encourage flexibility. In schema-less databases, there is no imposed structural restriction, only data to be saved.

Though every tool has it’s use case, sometimes things call for a hybrid approach.

What if you could structure some parts of your database and leave others to be flexible?

MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that.

In this tutorial, you are going to learn.

  1. How to design your database tables using JSON fields.
  2. The various JSON based functions available in MYSQL to create, read, update, and delete rows.
  3. How to work with JSON fields using the Eloquent ORM in Laravel.

Why Use JSON

At this moment, you are probably asking yourself why would you want to use JSON when MySQL has been catering to a wide variety of database needs even before it introduced a JSON data type.

The answer lies in the use-cases where you would probably use a make-shift approach.

Let me explain with an example.

Suppose you are building a web application where you have to save a user’s configuration/preferences in the database.

Generally, you can create a separate database table with the id, user_id, key, and value fields or save it as a formatted string that you can parse at runtime.

However, this works well for a small number of users. If you have about a thousand users and five configuration keys, you are looking at a table with five thousand records that addresses a very small feature of your application.

Or if you are taking the formatted string route, extraneous code that only compounds your server load.

Using a JSON data type field to save a user’s configuration in such a scenario can spare you a database table’s space and bring down the number of records, which were being saved separately, to be the same as the number of users.

And you get the added benefit of not having to write any JSON parsing code, the ORM or the language runtime takes care of it.

The Schema

Before we dive into using all the cool JSON stuff in MySQL, we are going to need a sample database to play with.

So, let’s get our database schema out of the way first.

We are going to consider the use case of an online store that houses multiple brands and a variety of electronics.

Since different electronics have different attributes(compare a Macbook with a Vacuumn Cleaner) that buyers are interested in, typically the Entity–attribute–value model (EAV) pattern is used.

However, since we now have the option to use a JSON data type, we are going to drop EAV.

For a start, our database will be named e_store and has three tables only named, brands, categories, and products respectively.

Our brands and categories tables will be pretty similar, each having an id and a name field.

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `e_store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `e_store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

The objective of these two tables will be to house the product categories and the brands that provide these products.

While we are at it, let us go ahead and seed some data into these tables to use later.

/* Brands */
INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Canon');

/* Types of electronic device */
INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Mobilephone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Camera');

The brands table

The categories table

Next, is the business area of this tutorial.

We are going to create a products table with the id, name, brand_id, category_id, and attributes fields.

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

Our table definition specifies foreign key constraints for the brand_id and category_id fields, specifying that they reference the brands and categories table respectively. We have also specified that the referenced rows should not be allowed to delete and if updated, the changes should reflect in the references as well.

The attributes field’s column type has been declared to be JSON which is the native data type now available in MySQL. This allows us to use the various JSON related constructs in MySQL on our attributes field.

Here is an entity relationship diagram of our created database.

The e_store database

Our database design is not the best in terms of efficiency and accuracy. There is no price column in the products table and we could do with putting a product into multiple categories. However, the purpose of this tutorial is not to teach database design but rather how to model objects of different nature in a single table using MySQL’s JSON features.

The CRUD Operations

Let us look at how to create, read, update, and delete data in a JSON field.

Create

Creating a record in the database with a JSON field is pretty simple.

All you need to do is add valid JSON as the field value in your insert statement.

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

The products table after adding televisions

Instead of laying out the JSON object yourself, you can also use the built-in JSON_OBJECT function.

The JSON_OBJECT function accepts a list of key/value pairs in the form JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) and returns a JSON object.

/* Let's sell some mobilephones */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

The products table after adding mobilephones

Notice the JSON_ARRAY function which returns a JSON array when passed a set of values.

If you specify a single key multiple times, only the first key/value pair will be retained. This is called normalizing the JSON in MySQL’s terms. Also, as part of normalization, the object keys are sorted and the extra white-space between key/value pairs is removed.

Another function that we can use to create JSON objects is the JSON_MERGE function.

The JSON_MERGE function takes multiple JSON objects and produces a single, aggregate object.

/* Let's sell some cameras */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

The products table after adding cameras

There is a lot happening in these insert statements and it can get a bit confusing. However, it is pretty simple.

We are only passing objects to the JSON_MERGE function. Some of them have been constructed using the JSON_OBJECT function we saw previously whereas others have been passed as valid JSON strings.

In case of the JSON_MERGE function, if a key is repeated multiple times, it’s value is retained as an array in the output.

A proof of concept is in order I suppose.

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

We can confirm all our queries were run successfully using the JSON_TYPE function which gives us the field value type.

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;

Add attributes are JSON objects

Read

Right, we have a few products in our database to work with.

For typical MySQL values that are not of type JSON, a where clause is pretty straight-forward. Just specify the column, an operator, and the values you need to work with.

Heuristically, when working with JSON columns, this does not work.

/* It's not that simple */
SELECT
    *
FROM
    `e_store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}';

When you wish to narrow down rows using a JSON field, you should be familiar with the concept of a path expression.

The most simplest definition of a path expression(think JQuery selectors) is it’s used to specify which parts of the JSON document to work with.

The second piece of the puzzle is the JSON_EXTRACT function which accepts a path expression to navigate through JSON.

Let us say we are interested in the range of televisions that have atleast a single USB and HDMI port.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

Selecting records by JSON attributes

The first argument to the JSON_EXTRACT function is the JSON to apply the path expression to which is the attributes column. The $ symbol tokenizes the object to work with. The $.ports.usb and $.ports.hdmi path expressions translate to "take the usb key under ports" and "take the hdmi key under ports" respectively.

Once we have extracted the keys we are interested in, it is pretty simple to use the MySQL operators such as > on them.

Also, the JSON_EXTRACT function has the alias -> that you can use to make your queries more readable.

Revising our previous query.

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

Update

In order to update JSON values, we are going to use the JSON_INSERT, JSON_REPLACE, and JSON_SET functions. These functions also require a path expression to specify which parts of the JSON object to modify.

The output of these functions is a valid JSON object with the changes applied.

Let us modify all mobilephones to have a chipset property as well.

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

Updated mobilephones

The $.chipset path expression identifies the position of the chipset property to be at the root of the object.

Let us update the chipset property to be more descriptive using the JSON_REPLACE function.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

Updated mobilephones

Easy peasy!

Lastly, we have the JSON_SET function which we will use to specify our televisions are pretty colorful.

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

Updated televisions

All of these functions seem identical but there is a difference in the way they behave.

The JSON_INSERT function will only add the property to the object if it does not exists already.

The JSON_REPLACE function substitutes the property only if it is found.

The JSON_SET function will add the property if it is not found else replace it.

Delete

There are two parts to deleting that we will look at.

The first is to delete a certain key/value from your JSON columns whereas the second is to delete rows using a JSON column.

Let us say we are no longer providing the mount_type information for cameras and wish to remove it for all cameras.

We will do it using the JSON_REMOVE function which returns the updated JSON after removing the specified key based on the path expression.

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

Cameras after removing mount_type property

For the second case, we also do not provide mobilephones anymore that have the Jellybean version of the Android OS.

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

We do not sell Jellybeans anymore!

As stated previously, working with a specific attribute requires the use of the JSON_EXTRACT function so in order to apply the LIKE operator, we have first extracted the os property of mobilephones(with the help of category_id) and deleted all records that contain the string Jellybean.

A Primer for Web Applications

The old days of directly working with a database are way behind us.

These days, frameworks insulate developers from lower-level operations and it almost feels alien for a framework fanatic not to be able to translate his/her database knowledge into an object relational mapper.

For the purpose of not leaving such developers heartbroken and wondering about their existence and purpose in the universe, we are going to look at how to go about the business of JSON columns in the Laravel framework.

We will only be focusing on the parts that overlap with our subject matter which deals with JSON columns. An in-depth tutorial on the Laravel framework is beyond the scope of this piece.

Creating the Migrations

Make sure to configure your Laravel application to use a MySQL database.

We are going to create three migrations for brands, categories, and products respectively.

$ php artisan make:migration create_brands
$ php artisan make:migration create_categories
$ php artisan make:migration create_products

The create_brands and create_categories migrations are pretty similar and and a regulation for Laravel developers.

/* database/migrations/create_brands.php */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBrands extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('brands');
    }
}

/* database/migrations/create_categories.php */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategories extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
    }
}

The create_products migration will also have the directives for indexes and foreign keys.

/* database/migrations/create_products */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // foreign key constraints
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('cascade')->onUpdate('restrict');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade')->onUpdate('restrict');
            // indexes
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }
}

Pay attention to the $table->json('attributes'); statement in the migration.

Just like creating any other table field using the appropriate data type named method, we have created a JSON column using the json method with the name attributes.

Also, this only works for database engines that support the JSON data type.

Engines, such as older versions of MySQL will not be able to carry out these migrations.

Creating the Models

Other than associations, there is not much needed to set up our models so let’s run through them quickly.

/* app/Brand.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    // A brand has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Category.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // A category has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Product.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    // Cast attributes JSON to array
    protected $casts = [
        'attributes' => 'array'
    ];

    // Each product has a brand
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // Each product has a category
    public function category(){
        return $this->belongsTo('Category');
    }
}

Again, our Product model needs a special mention.

The $casts array which has the key attributes set to array makes sure whenever a product is fetched from the database, it’s attributes JSON is converted to an associated array.

We will see later in the tutorial how this facilitates us to update records from our controller actions.

Resource Operations

Creating a Product

Speaking of the admin panel, the parameters to create a product maybe coming in through different routes since we have a number of product categories. You may also have different views to create, edit, show, and delete a product.

For example, a form to add a camera requires different input fields than a form to add a mobilephone so they warrant separate views.

Moreoever, once you have the user input data, you will most probabaly run it through a request validator, separate for the camera, and the mobilephone each.

The final step would be to create the product through Eloquent.

We will be focusing on the camera resource for the rest of this tutorial. Other products can be addressed using the code produced in a similar manner.

Assuming we are saving a camera and the form fields are named as the respective camera attributes, here is the controller action.

// creates product in database
// using form fields
public function store(Request $request){
    // create object and set properties
    $camera = new \App\Product();
    $camera->name = $request->name;
    $camera->brand_id = $request->brand_id;
    $camera->category_id = $request->category_id;
    $camera->attributes = json_encode([
        'processor' => $request->processor,
        'sensor_type' => $request->sensor_type,
        'monitor_type' => $request->monitor_type,
        'scanning_system' => $request->scanning_system,
    ]);
    // save to database
    $camera->save();
    // show the created camera
    return view('product.camera.show', ['camera' => $camera]);
}

Fetching Products

Recall the $casts array we declared earlier in the Product model. It will help us read and edit a product by treating attributes as an associative array.

// fetches a single product
// from database
public function show($id){
    $camera = \App\Product::find($id);
    return view('product.camera.show', ['camera' => $camera]);
}

Your view would use the $camera variable in the following manner.

<table>
    <tr>
        <td>Name</td>
        <td></td>
    </tr>
    <tr>
        <td>Brand ID</td>
        <td></td>
    </tr>
    <tr>
        <td>Category ID</td>
        <td></td>
    </tr>
    <tr>
        <td>Processor</td>
        <td></td>
    </tr>
    <tr>
        <td>Sensor Type</td>
        <td></td>
    </tr>
    <tr>
        <td>Monitor Type</td>
        <td></td>
    </tr>
    <tr>
        <td>Scanning System</td>
        <td></td>
    </tr>
</table>

Editing a Product

As shown in the previous section, you can easily fetch a product and pass it to the view, which in this case would be the edit view.

You can use the product variable to pre-populate form fields on the edit page.

Updating the product based on the user input will be pretty similar to the store action we saw earlier, only that instead of creating a new product, you will fetch it first from the database before updating it.

Searching Based on JSON Attributes

The last piece of the puzzle that remains to discuss is querying JSON columns using the Eloquent ORM.

If you have a search page that allows cameras to be searched based on their specifications provided by the user, you can do so with the following code.

// searches cameras by user provided specifications
public function search(Request $request){
    $cameras = \App\Product::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

The retrived records will now be available to the product.camera.search view as a $cameras collection.

Deleting a Product

Using a non-JSON column attribute, you can delete products by specifying a where clause and then calling the delete method.

For example, in case of an ID.

\App\Product::where('id', $id)->delete();

For JSON columns, specify a where clause using a single or multiple attributes and then call the delete method.

// deletes all cameras with the sensor_type attribute as CMOS
\App\Product::where('attributes->sensor_type', 'CMOS')->delete();
}

Curtains

We have barely scratched the surface when it comes to using JSON columns in MySQL.

Whenever you need to save data as key/value pairs in a separate table or work with flexible attributes for an entity, you should consider using a JSON data type field instead as it can heavily contribute to compressing your database design.

If you are interested in diving deeper, the MySQL documentation is a great resource to explore JSON concepts futher.

I hope you found this tutorial interesting and knowledgeable. Until my next piece, happy coding!

via Planet MySQL
Working with JSON in MySQL