Arts and crafts: developer version.

Context: spent all day making an ER diagram from an awful SQL query I was handed. Got sick of asking for it from the contractor who made the DB.

Yes, that's one query.

Imagine that level of tables relationship + the requirement that every transactions have to be a stored procedure instead of ORM or anything else

*swallowing migraine*

I've seen worse.. hell, I've wrote worse.. never felt compelled to commit it to paper though.. blugh :D

@devTea isn't ORM more of a prototyping tool?

@HitWRight using ORM for database transaction like EntityFramework

@devTea yeah I get that, but never managed to write any decent performance code, so I thought it was more of a prototyping tool for early stages of a software

@HitWRight the only time I see it having a poor performance is when you're selecting a multiple join tables, but even that could be solved with lazyloading etc

Edit: or eagerloading

GraphQL is the future and you just can slap it on top of existing databases acting as orm.

General idea
www.graphql.com

Product
www.prisma.io

🤷‍♂️still a baby query.
I've built wors.... I mean err umm, oh boy... "more complex" queries 😏

I once had to work with a patient management systems database.
750 tables in a postgres database..
Well, this was quite fun. Sometimes.

And the Er Diagrams were huge.

@HitWRight ORMs are definitely used in production. If performance is an issue you can always override the underlying code to better suit your use case.

@devTea
Add in no foreign keys, and all columns being non nullable and you're pretty much in my world.

Yes I'm in hell.

@HisAxelency hold on, I also worked on that kind of database before, no relationship from the sql, instead they explain it to us

@devTea yeah pretty much the same. Everything has 'relationships' they just aren't defined as a FK.

So you have to pretty much guess what table every GUID column is referencing to (column names are extremely abbreviated so you never really know what table it's from).

Really bad DB design tbh. Fun part is, the person who designed it thinks it's a good thing because users can add and delete all they want without DB level FK constraints ...

@heyheni what do you think the cons of graphql? I'm not shitting on it, I've used in on one of my past personal project. I just want to get some opinion about it

@devTea for me the big con was having to develop essentially role-based auth and deliver docs on that role basis. If you only had clearance to a section of the api, you shouldnt need docs to anything else. With RESTful implementations i just controlled access on the backend and put the docs out.

This could be a result of my own interpretation of the graphql and rest specs, but graphql does put a lot more control in the devs hands, even though it may create more work for me in the beginning.

Of course the graphql spec still needs updating for more specific use cases, but as a general idea i like it, and i like the design philosophy.

@Emphiliis 750 views, 170 underlying tables, 300 stored procedures, many-to-many relations. No indexes. No constraints. No fk relations. And the icing: its an oracle db.

@Techno-Wizard OK you won. Because oracledb is the worst.

@Emphiliis I truly hate its sql dialect. Give me anything else.

@Techno-Wizard even installing it is PITA. Docker to the rescue

@Emphiliis my place uses Windows Server so it's not as big of a problem but still really annoying apparently.

@Techno-Wizard ok, you can have the worst database ever face achivement here

@devTea I'd rather not to be honest. Mostly because I'm done in a month and won't have to deal with it ever again

@Emphiliis isn't oracledb the gold standard? If I understand correctly postgreSQL is based on oracledb.

@HitWRight I prefer MySQL, because it's plain and simple, but to be honest, mostly because I used it first. Postgres is not bad at all (maybe a bit bloated), but as I said, I'd prefer MySQL.

Oh and postgre ist much easier to install than oracledb.. All my experience with oracledb was just so disappointing. Beginning from the installation on Linux (I wasted a workday on it and then used a docker container). And the syntax, aside the basic sql stuff, does not quite catch me.

But despite all that negative stuff, it shines in functionality, there is a method for almost everything, that encourages using logic directly on the database, which is nice, but sucks to debug because the syntax sucks and no Oracledb sql dev writes proper comments.
(I don't need to know that the variable is being set or thst the Loipe goes over a specific list. I need to know WHY)

OK this message really got longer than expected. Sorry 🙏

@HitWRight oracle was the gold standard at one point, yes. However, we're talking about a different era where the SQL language specification was extremely empty. Oracle had to add a lot of their own features, many of them badly needed. However, being oracle, they did a pretty bad job of it. As the SQL spec grew to cover the things oracle had made, the spec itself defined much better practices and patterns for the SQL language. So I guess you can say that oracle was the first to really take the plunge into modern databases, but inevitably messed things up and did it terribly, because oracle. The plsql spec is sickeningly bad. LIMIT isn't even a thing. Like, seriously oracle? And the errors you get back from the server are just about as useful as Microsoft help pop-ups.