Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

4 Answers

up vote 1 down vote accepted

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"

share|improve this answer
up vote 1 down vote

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...

share|improve this answer
What did you mean by atomic? – chrisjlee Jul 20 '12 at 2:25
Data at its most granular level. – Roman Newaza Jul 20 '12 at 2:44
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.