Saving json in database

General Web Dev
1

Hello everyone,

I am busy creating something for myself and would like to keep it simple with the least amounts of tables.

So I was thinking about creating a table called db_storage.
This table will hold the following field :

id : which will hold a unique key
storage : will hold a json string with all data that needs to be saved.
tags : This could be optional but can be useful if you search for something.
created_at and updated_at : laravel includes this.

Why would I want to do it like this ?
I am thinking about a lot of flexibility not being constraint to certain tables and fields,
If I need to save something this can basically be done on the fly.

What do you guys think about this ?

2

I think that in most cases, this is a bad idea - but I suppose, as always, it’s going to depend on your use case.

The entire point of the system of many tables, each with their own fields and records, is so that records can have integrated relationships with other records, tables can be interconnected, etc. Also, queries can be limited. Imagine my database has 100,000 records in it, and I have to run queries on the database. Now imagine that it has 100 tables - that workload was just cut, by a lot.

All of that said… you’re probably the person that can answer this most effectively. Will you ever need to do things like search for all users with emails ending in X? Or for all people with a last logged in date of X? Or for items with a priority of X? If so, lumping the entire record into JSON is maybe not your solution.

If you just need to store a few things, and you’ve mapped it out and will have no issues querying or manipulating that data, and you don’t think you’ll spend tons of time or power parsing it all on either end… than I guess you’ve answered your question?

3

That or look into a NoSQL style database. Such as MongoDB, which are developed to be searchable but not have necessarily “true” columns or “true” table definitions.

They are typically better for large data, but could be used for smaller ones too, but your cost may be higher in doing so.

Here are a few interesting reads to help determine if MongoDB may be a good fit (or a similar NoSQL database)

https://www.mongodb.com/compare/mongodb-mysql (obviously a bit biased, but good read)

1 Like
4

I would advise against storing data like that in a relational database except in cases of caching. To which I would than say it might be a better idea to use Redis or any NoSQL database instead of MySQL. Why does the data model lack the definition that would allow it to exist in a standard relational schema?

5

Hello everyone, thanks for replying.
Reason why I wanted to store in json is because I am not restrained to columns.

I am kinda thinking about a way to save data in json for a api.
But cannot figure out a good way to do so.
Maybe I will need NoSql but I need to read more into it.

6

Depending on the circumstance there could be an argument made that is cached data.

7

Well depends, I want to make an api where a user can get all the page data using javascript.

But I will go over it a few more times to figure it out.

8

Working with data needs to be done somewhere. If not the database, the code.

When I first started coding my database tables were simple.
As things got more complex, the tables became “one big table with everything”.

I tended to write simple queries that returned much more than I needed, put that into an array, and then use PHP to weed out all the extra stuff.

It “worked” OK and as long as things were short and sweet I didn’t run into any problems.

Was it ever efficient - NO

I had a feeling for what Relational meant, but until I studied a bit of the RoR “has many” stuff I wouldn’t have been able to even begin explaining it.

All that may seem off-topic here, so to get to the point.

I have seen schema where a field stores separated values eg,
prod_ids "3,5,32,41"
And I have seen schema where a field holds serialized values (cough - WordPress - cough) eg.
post_data "{'post_id': 1, 'author_id': 2, 'author_name': 'Tom', 'content': 'My first post!'}"

Do they “work”? Yes. Efficient? No

Designing databases that have good table relationships and good indexing can be much more efficient than passing the task of doing the heavy lifting on to the code.

If this is for your own personal use, the data is and always will be small, you are better at writing code than understanding databases and crafting complex queries, and if for only a “one time” use, then IMHO doing what you are comfortable with and works for you is OK.

9

I would highly recommend building a properly normalized relational database in *most cases. Stuffing a whole bunch of crap into a single column has severe negative consequences that are difficult to undo once a project launches and needs to scale. I’ve dealt with so many short sited developers that do these types of things and I wish I could just go back and punch them in the face. Unless its a personal project someone else will ALWAYS end up maintaining something you did. Be courteous and anticipate scale.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.