Dave Ferguson talks about "Are your Database Relationships in a Rut?" in this episode of the CF Alive Podcast, with host Michaela Light.

"Perhaps we should just talk about, you know how some ColdFusion developers get stuck in a rut with their database or other pieces of CF ecosystem?

Dave Ferguson 6:54

Yeah, mostly, I mean, I would say Yeah, mostly like database specifically. Everybody, I mean, you get this happens with anybody in anything you do. When you get really good at doing one thing. People just tend to stick with it. Because it's like, that's the train just the way I know how to do something. And I'm going to do this because it just makes my life simpler. But at the same time, you're almost doing yourself a disservice, because you're not learning something new that can make your life even easier. So you don't want to have that stuck in a rut of I'm going to do, I'm gonna do the same thing at that 10. Well, you want to do the same thing over to you, you're really good at it. But getting really good at something allows you to learn something else easier then. So if you just stick to one thing, you're never going to get down that I'm going to learn something else easier, because I'm just sticking with one specific thing."

Show notes

CF Rut?

Don’t get stuck in a CFer rut

Just because you have always done Try new ways too

Hence trying different approaches

Definition of “insanity”

What relational databases do most CFers use?

SQL Server

MySQL

Is there a better way?

Most systems don't need an elaborate, and possibly expensive, relational database. Most can get by just fine with something else. 

Can horizontally scale-out to accommodate large data volumes

Documents typically align better with code objects

Evolve as the app / data evolves without restructuring

Types of database

Hierarchical databases

Network databases

Object-oriented databases

Relational databases

Links data via Primary and Foreign

keys

Standard T-SQL query language

Ridged schema/structure

Referential Integrity (ACID)

NoSQL databases

You don't have to store your data in predetermined columns

each row can have a data structure the other rows don't

Examples

MongoDB

Apache CouchDB

MarkLogic

Azure Cosmos DB

Couchbase

Key value databases

(a type of NoSQL)

Amazon DynamoDB

Oracle NoSQL Database

InfinityDB

Redis

Wide-column Stores

Google Bigtable

Amazon DynamoDB

Apache Accumulo

Apache Cassandra

Apache HBase

Why

Column formatting and names vary row to row

Columns are stored separately on disk

Data searching can be faster

Graph Databases

Neo4j

ArangoDB

Dgraph

OrientDB

Amazon Neptune

Intensive data relationship handling. Relationships are treated as a first-class citizen

Structure and schema of a graph model can flex as applications and industries change

Database can e

Transactions pros and cons

Relational databases have transactions - a group of SQL statements either all succeed or are all rolled back. Is this always best for app?

Downsides of NoSQL

No / Minimal ACID Support

ACID =

Atomicity, Consistency, Isolation, Durability

⇒ Transactions 

Little to no standardization between NoSQL products

NoSQL uses "Eventual Consistency" over transactions

Avoid NoSQL for

Banking

Online gaming

Rights Management

Complex / Dynamic querying

Use

Collection = table

createCollection

Document = row

objects

What database type is more appropriate than others for certain data?

Where does using a hybrid of databases makes sense and how that would look to a system?

How easy is it to use alternative databases with CF 2021?

ACF 2021 Package manager

Install MongoDB locally

Use MongoDB Atlas

DO MongoDB

CAP Theorem

Consistency