CODEWITHSUNDEEP
phpmysqldatabasejson
user1302430
user1302430

Reputation: 352

PHP/MySQL - Using json encoded data instead of multiple columns in database

I was wondering if using some sort of serialized data whether it be json encoded or serialized etc. was better than using multiple columns in a mysql database.

Example: If I have a "users" table, some columns would usually be: id, fullname, username, email, password etc.

But what if I used json data instead of having all these columns. Could I use just a 2 columns in my db table (id, data). So I could do something like this in php:

json_encode(array('username' => $_POST['username'], 'password' => md5($_POST['password'])));

And then insert it into a table with a column of "data" which would hold the json encoded array.

Later on in the script, I could just retrieve the 'data' column and use json_decode($stuff_from_db, true)

I'm sorry if this is a dumb question, I'm not really very knowledgeable about mysql and how it scales etc. But I would like to know if this would be a good or bad idea.

Thanks

Upvotes: 0

Views: 1477

Answers (4)

Aaron W.
Aaron W.

Reputation: 9299

No, it wouldn't be a good idea and that would take the Relational out of RDBMS. You wouldn't be able to quickly query anything out of the table without having to parse the results of each row first. On a small scale this would be okay, but then MySQL wouldn't be the database to use if this is what you had in mind for storing data.

Imagine trying to get the following out of your database if you stored it using serialization or encoding with JSON:

Get the number of users with the first name "John"

Upvotes: 1

Abdulaziz
Abdulaziz

Reputation: 2211

Yes, it's a bad idea. it's really inefficient to work with such a database, like querying WHERE or JOIN queries, it's probably impossible to do so.

Also, if you're willing to create an API that gets some information from your JSON encoded database, it will take more lines of code to convert your JSON encoded data to something like XML.

One little thing, you have to select all information about a user when you just need something specific, like a full-name to display, which is again, inefficient.

Upvotes: 1

Josnidhin
Josnidhin

Reputation: 12504

For certain type of data it might work. But for the example that you have given it is not a very good Idea. If you store it as json or serialized value then you wont be able to search.

How would you properly do the following query

Select * from <table_name> where user_name="<user_name>"

You could claim that wild card search is possible. But I wouldnt do it.

I also think if you have the correct scenario you should store json instead of serialized values. If you store serialized values then other system if they exists wont be able to interact with this value given they are written in a different language.

Upvotes: 0

Roman Newaza
Roman Newaza

Reputation: 11690

You can do it, but that would be very bad design because your data is not atomic anymore. It's not good for search as table needs to be scanned...

Upvotes: 1

Related Questions