Ask HN: Things you wish you knew about databases before building an app?

[praveenweb]

As a frontend or fullstack developer, what are some things you wish you knew about databases for building an app.

This could be anything like using connection strings, CLI tools, user Grants, GUI, writing optimised queries, adding indexes for performance, how SQL views can be leveraged etc.

[bwh2]

The biggest thing is avoiding database structures that will be difficult to work with later, requiring data migrations to escape the dilemna.

The book SQL Antipatterns is a good read on this subject. But realistically, I think you need to work in a few production databases and experience these problems before you deeply understand them enough to understand why they are antipatterns.

[taffer]

- You cannot have a good database design without understanding the business requirements first

- Designing the code around the database leads to cleaner designs than doing it the other way around

- Almost all columns should be NOT NULL

- EXPLAIN helps you understand what the database does under the hood

[praveenweb → taffer]

Agreed with all 4 points.

EXPLAIN ANALYZE output is cryptic enough for beginners to get any meaningful information out of it. Your thoughts on where to get started as a frontend developer?

[dangerface → taffer]

> Designing the code around the database

This is great advice

> Almost all columns should be NOT NULL

Can you give your reasons for this? I never really understood the point of null tbh

[bwh2 → dangerface]

A few things I've seen with null values:

* You're storing multiple types of data in the same table (often paired with a column indicating type). Some types have nulls in a column, other types have non-nulls.

* You've created an arbitrary limit on the data. Ex: a books table with author1, author2, and author3 columns.

* You add complexity to your queries making them less maintainable. For instance, you might start injecting COALESCE, IS NOT NULL, empty string checks, etc. all over the place.

* You need to be more careful with math. Ex: what is null + 10?

Null isn't universally bad, but it comes with issues you need to be aware of.

[dangerface → bwh2]

Good reasons I try to give all my columns a default and have been trying to cut down on the use of null in general. More when programming in for example javascript the multiple null thing really annoys me is it null, NAN or undefined or "" who knows, so much conditional logic to arrive at nothing lol.

[DarrenDev]

Never store two different types of information in the same table, even if the structure is identical. They may be identical today -- they won't be in two years time.

If you're unsure about this, ask yourself if your table has a column called 'type' or something similar. If it does, a big migration awaits you a year or two down the road, and you'll have a lot of pain between now and then.

[joshxyz]

Always do a reality check if this xyz thing directly relates to the business goals, in a timely manner.

[klausnrooster]

Can't find good link now, but the fact SQL is based on a kind of Bag Logic as opposed to the logic of Sets.