Disadvantages of storing all the columns inside a JSON column.

what are the disadvantages of storing all the columns of a table inside a single JSON column ?

e.g for a user table instead of having this

    Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('firstname');
            $table->string('lastname');
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->string('password');
            $table->timestamps();            
            $table->rememberToken();
        });

Just do this

    Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
        $table->json('attributes'); // contains all the fields
            $table->timestamps();   
            $table->rememberToken();
        });

any recommendations

D9705996

7 years ago

Level 51

I would advocate always using columns for structured data e.g. firstname, email as you know the data type, size, etc.

A relational database is designed for this sort of data and will handle queries optimally. The json datatype is handy for when you data doesn't follow a structure. If this is a small part of your overall application then fair enough.

However if you are planning on storing all your data in this way you should really be using a NoSQL database like MongoDB as it's designed for this use case.

The other major drawback is with performance of json columns. They are likely to always be slower than native datatype in 99% of circumstances but can be optimized with indexes on virtual columns. The query syntax is also a little bit more complex

https://laravel.com/docs/5.7/queries#json-where-clauses

From a laravel perspective you are also going to add complexity to your seeders, factories and migrations.

The benefits you get are pretty much zero

2 likes

Grelav

7 years ago

Level 2

Pretty declarative post @D9705996

So in brief, this approach isn't good for products with customized properties for example. NoSql Databases are optimal for e-commerce website / systems.

but JSON field can be useful for combining less used fields like gender, age, address.

true?

D9705996

7 years ago

Level 51

@GRELAV -

NoSql Databases are optimal for e-commerce website / systems.

Not necessarily, you can't directly attribute any system to a particular database (or any other technology). You need review the needs of the system and make a technical decision based on those needs

but is useful for less used fields like gender, age, address

Not really, I would store age as an integer, store gender as an enum and address in a separate table related to users with the address broken into structured fields in most circumstances but again would depend on the specifics of the system.

Please or to participate in this conversation.