Why doesn't Jow Forums ever talk about relational databases and SQL?

AWS Athena is pretty cool. Dump flat files of several formats (csv, json, etc.) in to S3 and Athena provides you an SQL endpoint.

Biggest meme of the 90's. EVERY piece of "enterprise" software had to have an RDBMS back end.

Wtf are you talking about. Every chain store has to have this type of shit under fucking control dude, those server cost the up to 10k and they won't update server every fucking year just because an admin forgot to use the second CPU.

Clients
Distribution center
Shops
Accounting
Employs management

And more shit are crosslinked database/tables, servers are up all day and have probably transactions in line.

Up to 10k... and then 80k in licensing.

Most enterprises need some kind of database.

>RDBMS's have a learning curve like a brick wall if you want to use it as more than a bit bucket
Shit, I was supposed to learn more than just grabbing rows? I'm a brainlet and prefer to post-process the data in an actual language; there's probable a huge performance penalty but nobody is complaining yet

Fuck that hurt.

This is why so many web developers prefer nosql databases. It saves them from having to learn database theory and lets them pretend they are smart when they reimplement basic rdbms functionality poorly. Or they use an ORM and pretend their database doesn’t exist.

I've just started learning about SQL and RDBMS. How much processing should you be doing in $RDBMS before you handle shit in a general purpose programming language?

>I'm a brainlet and prefer to post-process the data in an actual language
As an analyst in the consulting wing of my company, I once helped out a junior C# programmer with some terrible performance he was having. He was supposed to match rows from two tables, which weren't that large by the standards I was used to. He said he had left the thing running overnight and it still hadn't completed.

After some painful questioning about the table structure and foreign keys, we wrote a simple join clause with some additional WHERE filters and the whole thing ran in less than 10 seconds.

Learning even the basics of using your trusty DB is a must.

>
>Up to 10k... and then 80k in licensing.
Pic related. Pricing for these commercial offerings is simply insane. We have one client who's been using MySQL 5.7 for their ERP and analytics workloads. Even though it lacks some basic analytic functions and grinds to a halt when a UNION is concerned, it gets the job done.

I have no idea why open source databases like MySQL / MariaDB and PostgreSQL aren't that popular within small to medium-sized corporations.

Attached: Screenshot_2019-08-28 SQL Server—Pricing and Licensing Microsoft.png (377x355, 12K)

As much as you can. You can write your own stored procedures for more complicated queries, and many RDBMS have support for embedding scripting languages like Python or Lua into them.

lol this

>I have no idea why open source databases like MySQL / MariaDB and PostgreSQL aren't that popular within small to medium-sized corporations.
I assume it is durability and support. When something explodes, you can always call your vendor to help you clean it up. But if you set up a FOSS DB yourself, then you are on your own.

>I have no idea why open source databases like MySQL / MariaDB and PostgreSQL aren't that popular within small to medium-sized corporations.
Anyone capable of using those well typically wants pay and benefits the small shops can't match. Sysadmins have the same problem - small shops are stuck with duct taped, overpriced Windows infrastructure because they can't pay the money for competent Linux admins. Microsoft's entire business model is making half baked software juuuust usable enough by idiots that the payroll savings offset the license fees.

Most erps are just built on mssql. Its either you oay for systems or devs and admins. Cost of doing buisness.

>no clue why open source x isn't popular in small to medium sized businesses
I'd imagine it's a combination of a lack of advertising, a lack of a supporting entity, and the stigma of Open Source being amateur hour. Never underestimate the ego of the entrepreneur.

Because it's shite. They got new shit now don't you know? Like NoSQL and whatnot.

Because MySQL costs money and everyone here is NEET.

SQl databases just work exceptionally well. there are several good options. they have managed to stay in a role and not become bloated. they solve a hard technical problem that most programmers can't replicate. they use some solid CS theory and solve important business problems.

because of all this there isn't much to discuss until hipster fags had to start brining mongo into conversatik s

so how do you store data in a way that is safe for multiple processes on your system to access simultaneously? is it ACID compliant?

>Why doesn't Jow Forums ever talk about relational databases and SQL?
Does databases run gaymes? If not, then why bother?

>mongo
I haven't seen much mention of mongoloid is a while. Did the nosql bunch realise they were solving the exact same problems all over again, and just abandoned it?

yeah I think the mongo hype did die, it's definitely been replaced by some "non normalized data" hype and strange caching databases because "optimization is stupid" but coordinating redudant sources of the same data is smart.

Mongo changed their licensing so that any cloud provider offering hosted Mongo was basically obligated to open source their entire cloud infrastructure soup to nuts. AWS and Azure told them to fuck off and pushed their own NoSQL implementations.

Nosql is a meme perpetuated by amazon and co. to sell elastic-buzzword services.

Why talk about it? its the thing you install in a hour with replication and other shit then forgot it until the server dies

based. a "no SQL database" should only mean a text file or key value store.

imagine calling it "sequel" instead of S-Q-L

Even most "IT people" don't know databases very well. Let me give two examples of the American government using your tax dollars efficiently. In particular, I'm speaking about state level government.

One example was a project being done directly by the state. They needed to store information on people who had gotten in trouble and violated some regulations. Their database didn't have primary keys. We asked them how they can uniquely identify someone without a primary key of any kind. The state replied that they were going by first and last name. We asked "What do you do in the situation where two people share the same name?". I shit you not, the state literally didn't even consider that possibility. That's insane to me. Not only is that extremely common, but for fucks sake, it happens in the same family where you'll have John Smith Sr and John Smith Jr. A few months later, the project was cancelled and replaced with a commercial solution.

The other example is a very similar story. Another state level government shared their data with us and proclaimed that "person_id" was their primary key. As soon as we got their data, we noticed that there were multiple rows with the same ID. The reply we got back was that this was "by design" and not a bug. The state had outsourced their work to a contractor and no matter how many times we explained that, by definition, a primary key doesn't have duplicates, they didn't understand it. I later found out that this wasn't even a "lowest bidder" contract either. The state shelled out huge sums of money for this.

I basically get paid a pretty comfortable salary (also on tax payer expense) to do what amounts to shitposting, except instead of doing it on Jow Forums, I'm emailing other parts of the government to tell them their database design is shit. Nothing will make you libertarian faster than working directly in a job where you see how tax dollars are spent.

We're too dumb

u think everyone here is a programmers ?

Shit in the loo, pajeet

>tfw you manage to make a really cool optimal query without using a stupid ORM

Attached: 33a53f47fe7e0cabc48440001f8ddea2.gif (500x307, 462K)

data science actually requires you to think.

how do I become a dba?

I just started using mongo today and I'm finding it really easy to integrate with, is there a better option out there?

thanks to PostgreSQL its a solved problem

Did this yesterday. Wrote a query in postgres for a custom imageboard I'm developing. It has to fetch 10 threads from a board and, for each thread, fetch the last 3 replies with support for more than one image each. Shit's fast.

CTE + Lateral Join = win

Yes. For a little playground database.
I'll never understand why anyone uses MySql or Maria instead of Postgres.

Fuck, this is literally the problem I'm stuck on right now:

blow the CTO's cock

The query I came with was:

with thread_ids as (
select
t.id
from t_post t
where
t.parent_id is null and
t.board_id = $1
order by t.last_bumped_at desc
limit 10 -- select only 10 threads
),
posts as (
select
p.*
from thread_ids t
join lateral (
select
p.id,
p.parent_id,
p.body,
p.body_formatted,
p.poster_name,
p.created_at
from
t_post p
where
p.id = t.id or
p.parent_id = t.id
order by
p.parent_id asc nulls first,
p.id desc
limit 4 -- 1 (op first row) + 3 (replies in reverse order)
) as p on true
)
select * from posts;
-- here you add other joins, like joining images, user data etc;

It won't totally solve your problem since your thread information and replies are stored separately, however this is easy to tweak if you instead of only using thread ids in the first CTE like I did, fetch the posts contents and do a union between them during the second CTE. Hope this helps!

not him, but that's really nice of you to share. You're a cool guy :)

Attached: 1566425281697.jpg (696x720, 30K)

Thanks. Hopefully it outperforms my mostly-manual approach right now.

Thanks! :^)

>new job
>open DB schema
>thousands of stored procedures implementing business logic
How do I not kill myself?

>Nothing will make you libertarian faster than working directly in a job where you see how tax dollars are spent.
This guy gets it.
State govt worker here. Just getting into SQL on work (on temp transfer from network and server team). Today got exposed to some big stored procs running on Visual Studio. Shit seriously fucked with my head

Attached: 1561250642161.jpg (1070x1071, 107K)

i shot my load to that awesome indentation

I guess we work at the same place then.

My current job is a credit card processor. We do most of our transactions in SQL procedures in a DB with millions of payments/client data with NO FOREIGN KEYS at all.

>My current job is a credit card processor.
Fug, that's the same shit we do. We've got some foreign keys though.

It's the way G'd intended us to craft SQL.

Because they spend all their time implementing hello world tier apps and gaems in C or C++ or sepples or whatever "leet" language they're using. That shit doesn't need a database.

Shitty area, eh? Wish I could do something more creative.

>Shitty area, eh?
Yeah, that's been my experience so far. Quitting next week desu, fuck these Pajeets.

most of em are for freeware

Actually converted a lot of our C# codebase to T-SQL, just made some user defined CLR functions for whatever stuff it's lacking, like regular expressions, aggregating and splitting strings back into tables, Levenshtein distance. The speedup is immense, it's really built to use all the cores effectively. It's faster to bulk-insert a table, process it and download it than to process it client-side. Some of its shit is total wack though, like string collations(just use fucking Unicode), string columns having fixed length, strings being padded with spaces on the right leading to some nasty surprises during comparisons, etc. And weak typing doesn't fit well its supposed performance-oriented design, some implicit conversions can totally ruin performance.

Also it tends to make merge join when hash join would make way more sense. Thankfully query hints exist.

MySQL is free...

Databases are complete memes, and the only reason they exist is because businesses use them for the most mundane retarded shit in the first place. No sane intelligent person sits down and goes "gee, I wonder how many databases I can make today".

Where do you store your data, senpai?

MySQL is part of the LAMP stack, a group of open-source software that includes Linux. So if you browse Jow Forums you basically have to like it. Pic related

Attached: LAMP_Stack.jpg (616x822, 66K)

He doesn't store data. He doesn't have any data to store in the first place.

He does, however, have a 60% mechanical with RGB, an RGB gaming mouse with the same style as the bat mobile, a gaming chair that makes you wonder if he also sleeps in a race car and he refuses to touch anything made by Apple.

12 year old detected

In a hash table with RLE you troglodyte,
>unironically getting this mad that someone doesn't like databases
OH NO NO NO NO NO NO NO

I have also heard this in my db classes, to offload as much of the workload to your db as possible. But does that still apply in a micro service infrastructure? I mean in most distributed system the throughput of the rdbms is going to be your likely bottle neck if you have a I/O intensive application.

>TFW learning about functional dependencies and normalization right now
Are there any resources that explains or elaborates these concepts very well?

what is there to know besides the primitive core SQL itself, which is a small tool in the very large toolbox of IT?

so there's basically not much to talk about

Maryland?

Jow Forums thinks of a simple join as "totally complex shit".

In a true micro service architecture you will have a separate database for each node anyway.

Isn't it MariaDB that is still free?

From a scale of 1-10, how shit is my DB architecture? I'm just starting to flesh out the tables.

Attached: sqldiagram.png (1467x709, 29K)

I feel like relational databases are kind of weird. For 99.9% of projects (perhaps even 99.999%), it's a plug and play thing that just werks. And most people use a library that simplifies the schemas and queries so it's pretty easy to work with them.

The 0.0001% that actually needs to optimize their databases probably doesn't post here.

What program is this for mapping out sql?

A shit one, seeing how it's linking user id to role id

So the idea of it is a party finder for startups. Talent can look for new startups to join and startups can search for applicants. The idea is to give a lot of tools to both parties so that they can reliably find what they want (e.g. for startup owners they can have preassigned leetcode questions to give to applicants and for talent, we'll have a validation system that puts startups in tiers ranging from LITERALLY WHO to Uber-esque status).

Right now I just started designing the tables needed for the front page mock (pic related). Since right now, all I have to go on is a listview of parties with needed roles shown as icons (e.g. frontend, backend, UI), I started there.

Attached: partyfinder.png (620x409, 85K)

It's not linking user id to roles. The table just hides the connection.

Attached: sqldiagram.png (1391x651, 30K)

THE PROGRAM YOU USED TO VISUALLY FLESH OUT YOUR TABLES, WHAT IS THE NAME

And you don't see the problem in that?

Oh I thought you were asking about the purpose of the DB.

The program is a site dbdiagram.io

Just use any flowchart software, or try sqldbm.com but they jewed it up now and you can only have one free project.

draw.io is alright too

party_members is an association/bridging table that forms a many to many connection between parties and users. Parties can have many users and users can be part of many parties.

Should I be doing something else? What would you recommend?

I'll have a look at both.

I'm recommending a flowchart drawing that doesn't show the wires going to the wrong rows. Yes, you understand how it works, but for somebody else working on the db who can't read your fucking mind, it makes no sense.

relational databases!!

Attached: image.png (201x250, 7K)

Yeah, it kind of sucks. I can't show basic things like composite keys either (open_roles has a composite key of party_id and role_id).

What would you recommend then? Anything that is pretty simple and looks sleek and not like something out of a Windows 95 commercial?

Attached: sqldiagram2.png (1451x795, 32K)

shit. all primary/foreign keys should be uuids. there's literally no reason they should ever still be an integer. also, i don't quite know how it's going to be used, but that "quantity" column is supposed to be a count of another table or something, it's shit, get rid of it.

>all primary/foreign keys should be uuids.
That's not a bad suggestion. They were longs but I thought ints were going to be good enough for my small project. It can grow in scale though.

>also, i don't quite know how it's going to be used, but that "quantity" column is supposed to be a count of another table or something, it's shit, get rid of it.

So open_roles is one to many relationship where parties have many open_roles. These roles are stored in a lookup table and it's just a dictionary of kind where 10 or so roles are defined. That's the reason for the quantity field.

I like SQL but I never figured how to properly implement triggers in mySQL so I committed sudoku.

Your boi pussy is boring.