Hacker News Reader

When I was a younger developer, knowledge of SQL and how your database processed data was an expectation of everyone around me in the field.

The older I get and end up around younger developers, I find that this has become a rare superpower. These days, if I’m consulting on a problem for somebody I can almost assume that it’s a database problem. Either needs to be tuned, indexes are missing, data has been corrupted or duplicated due to lack of constraints, excessive single writes or any number of other issues.

I never thought something that was always a required skill set to suddenly become rare and shocking.

If it makes you feel better, my company employs a lot of engineers (not developers, more like electrical and mechanical engineers). We all do tons of analysis with our gargantuan database and write lots and lots of SQL. It too amazes us when we deal with other departments that are mostly Excel + Python + APIs. They write a lot of unecessary code where a separation between generic programming language and database would make things a lot more efficient IMO.

I will defend that a little. We are all, to an extent, victims of circumstances. All those Excel+Python+API hacks are result of individuals having to deliver results, but with limited tools at their disposal.

> Also, some young devs come up with crazy ideas (for instance, we should not use foreign keys).

My favorite was "we shouldn't use indexes it slows down the DB" by a junior in regards to us collectively telling him to add indexes to a MondoDB collection. When we told him to research it, we meant how to do it.

One of our MySQL instances somehow ended up with two copies of the same index, and any query that tried to use them ended up being a lot slower than it should have been. Dropping the copy restored it to normal speed.

Wow, I don't even know how to pull that off. I would of thought it wouldn't let you do that!

At least in postgres, you don’t need to provide a name, in which case it seems to do no dedup check on the create index. Quite annoying if you’re doing changes ad-hoc

> The data model should really always be validated by someone with more knowledge and experience.

Are there resources (e.g., books) that one can get ideas about good data modelling?

Any database primer e.g. intro-level textbook would do, for the basics of relational integrity. But try finding a developer these days who has read a book or shows any interest whatsoever in writing SQL. Most of them just want to work with it behind an abstraction layer.

TONS.

Now, there are I feel couple of different approaches: There's data modelling from a data scientist perspective, which I have limited perspective of. But there's the meat & potatoes of data modelling from regular development / database administration perspective, and honestly any course, book or video worth its salt will start with that. Pick up any well reviewed database administration primer. You can choose any level of detail or depth. One way to get there is look at textbooks from university intro courses. I would use entity relationship or 3rd degree normalization as key words to check what depth it goes into, but I'm old and cranky so take that with a grain of salt :)

e.g. https://www.coursera.org/learn/database-management

One of my favorite books to throw out for noobs is both a pattern and antipattern book for database folks. https://www.amazon.com/SQL-Antipatterns-Programming-Pragmati...

It's possibly a bit more abstract or philosophical than what you're looking for, but William Kent's _Data and Reality_ is awesome on data modelling.

Discussed by Hillel Wayne at https://buttondown.email/hillelwayne/archive/why-you-should-..., HN discussion at https://news.ycombinator.com/item?id=30251747.

There is the book Enterprise Rails that has a section on data modeling. Even if you don't use Rails, that section of the book is worth it.

The classic is Ralph Kimball's "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling." Be sure you get the 3rd Edition.

>There is a continuous stream of bad ideas being implemented. And when a bad idea gets implemented, it is really hard to undo it and it tends to just create more chaos.

This describes all aspects of software development.

Textbooks advocate a split between DA data admin vs DBA database admin. The first manages the model, the second manages the tech. You sound like nobody is doing the DA role, so all misery drops on the DBA head.

Thank you for being the DBA that helps devs. I found myself similarly frustrated when I was a developer. Now I work in DevOps, and I still cannot get traditional DBAs to deliver. :(

"we should not use foreign keys"

Some Microsoft enterprise products were like this (e.g. Dynamics AX) - try understanding 5000 odd tables without any foreign keys!

All database designs have foreign keys whether they are enforced in the database or not. If they are not obvious from the documentation, that is a documentation problem.

What is the idea behind no foreign keys? Seems quite crazy.

Surely that defeats the whole relational database concept?

Without FKs you have no clearly defined relations.

Is the idea that you have no constraints imposed by FKs, but tables still have some sort of informal relation?

If you have a super high write throughput table (say a constant stream of sensor data) foreign keys and extra indexes can slow it down to too much. Besides that, you should always use foreign keys.

There were no foreign key constraints at the database level - the application must have been holding and enforcing the relevant constraints.

A bit of a pain when you are looking at the SQL database trying to understand how it is structured so you can extract data in a meaningful way.

Foreign keys as a concept are a critical portion of a data model, but may reasonably choose to not enable them via schema constraints due to write performance, scaling, sharding, or replication issues.

Tangential, but this is one of the main reasons I never liked Ruby on Rails with Active Record when I first took a look at it. Not having DB-level foreign keys by default always drove me insane. Isn't maintaining data integrity/correctness half the point of using a SQL database lol?

> we should not use foreign keys

Not using foreign key constraints is a good idea. Occasionally. Very occasionally.