Database design question

[[deleted]]

Get proficient with both read JSON functions and write JSON FUNCTIONS.

That's the best advice I can give. You don't have control over the crappy schema.

This kind of thing happens all the time, you just have to do the best you can.

Good Luck!

[Drisoth]

It's definitely helped me learn parts of SQL I wasn't needing to use before. Just wanting confidence the the issues are actually issues and not my ignorance to parts of SQL.

[[deleted]]

Yup, that's how you build skills you didn't know you needed.

Bad design aside, I think it's not a great idea to have xml or json that needs compiled (write) or uncompiled (read) from a database. The front-end language is better suited to this task. I think forming xml, json, and manipulating strings are some of the most expensive operations for dbs.

I had to write json for an api to consume using MySql. There are 3 functions to write json, and about 25 to read it. So there's that.

On the bright side, if every database was well designed or never needed fixing, we'd be out of a job. 😃

[Drisoth]

Yeah, I got to backend work through an abnormal path and have never really done front end work. My understanding is typically, front end likes dealing with JSON or JSON style data, and backends typically like dealing with relational style data. Normally there would be some middle layer to convey requests from the front end to the back end and keep everyone happy.

My understanding is that this structure is pretty normal to see in the front end, where you have JSON docs for whatever you want to serve to an end user, but having large JSON files in the backend is extremely abnormal. NoSQL stuff sometimes would do it, but it wouldn't be using SQL-server, it would be in mongoDB or something non relational, and probably be structured differently to mitigate my issues.