This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the design of a database, imagine years later what someone looking at it might want to know if having to rip all the data out into some other store. Having migrated many legacy systems, I tend to sleep better when I know the data is well-structured and easy to normalize. In those cases, I really don't care so much about the apps. If I can sort out (haha) the data, I worry less about the new apps I need to design. I have been known to bury documentation into for-purpose tables...that way I know that info won't be lost. Export the schema regularly, version it, check it in somewhere. And, if you can, please, limit the use of anything that can hold a NULL. Not every RDBMS handles NULL the same way. Big old databases live a looooong time.
Things to know about databases
61–70 of 247 posts
Re: Things to know about databases
#62#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
Isn't this like saying you can solve every programming problem you have with ? Of course you can, but aren't there any cases where the tradeoffs outweigh the benefits, even if it's about something selfish like ergonomics or, dare I say, fun?
Re: Things to know about databases
#63#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
Isn't this like saying you can solve every programming problem you have with ? Of course you can, but aren't there any cases where the tradeoffs outweigh the benefits, even if it's about something selfish like ergonomics or, dare I say, fun?
I agree with you. However, conversely I don't see anything that proves him wrong. Databases are not like programming languages. There is a reason why we don't use punch cards anymore.
Re: Things to know about databases
#64Earlier quoted context omitted.
There are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in…
> usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently,…
> schemas
The confusion there is due to the fact that non-R-DBMS (particular when referred to as noSQL) can mean several different things.
In this context I was replying to a comment about not knowing the shape of your data which implies that person was thinking about solutions that are specifically described as schemaless, which is what a lot of people assume (in my experience) if you say non-relational or noSQL.
That is the sort of constraints I was meaning: primary & unique keys and foreign keys for enforcing referential integrity and other validity rules enforced at the storage level. There are times when you can't enforce these things immediately with good performance (significantly distributed data stores that need concurrent distributed writes for instance - but the need for those is less common for most developers than the big data hype salespeople might have you believe) so then you have to consider letting go of them (I would advise considering it very carefully).
Re: Things to know about databases
#65Earlier quoted context omitted.
There are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in…
> usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently,…
> There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space.
If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases).
I agree with GP that this is a central problem to solve, not something to figure out _after_ you write software. Build your house on stone.
Re: Things to know about databases
#66#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…
EdgeDB looks promising. Postgres under the hood so you know it's stable.
Re: Things to know about databases
#67Nice post, though for the indexing "introduction-deep-dive" I would still recommend newbies to look at https://use-the-index-luke.com/ .
also check out rick james's mysql documents http://mysql.rjweb.org/
I send those 2 links to coworkers all the time
Re: Things to know about databases
#68This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…
> Realize that any reasonably used database will likely outlast the applications leveraging it.
I love this statement. It's true too, having seen a decades-old database that needed to be converted to Postgres. The old application was going to be thrown away, but the data was still relevant :).
Re: Things to know about databases
#69Earlier quoted context omitted.
> usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently,…
> There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases). I agree with GP tha…
>If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases).
I agree with your comment that it's a central problem to solve and that both options, throwing out data or map old records onto new semantics, is an endemic choice both stacks need to make. I don't agree that it's always possible to solve entirely up front though.
In my experience, it has been less so about whether the storage engine is schemaless or not, even many modern nosql stacks now ship with schemas (e.g. MongoDB). I think the differentiation I make between these platforms is mostly around APIs. Expressive, flexible semantics that (in theory) let you move quickly.
As an aside, I also think the differentiation between all these systems is largely unimpactful for most software engineers. And the choice often made is one of qualitative/subjective analysis of dev ergonomics etc. At scale there are certainly implementation details that begin to disproportionately impact the way you write software, sometimes prohibitively so, but most folks aren't in that territory.
Re: Things to know about databases
#70#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…
What is a pleasant API? For what kind of data?