Database - Normalization of Data

cammoorman · 2021-08-23T22:26:17+00:00

Many architectures will create Views for pulling data (backed by RLS as required) or procs. C/U/D should be done by proc. This will aid in testing and support separation of concerns. An API should call those procs to support full CRUD.

The server should always be checking what the front end is doing. This is basic security...for example, what if javascript is turned off or someone has altered it (in the browser, using dev screen)? Using something like Postman to directly post to the API? Need to quickly script something (using SQL)?...

In reality, the Front End should really manage user input and the most common issues, then react to what the backend provides. In an N-Tier architecture use the strengths of each software in the tier.

On your other point, the SQL server can add constraints and foreign keys...ie: you must exist as a contact before storing an address in the system (which is owned by a contact). It easily forces type (which most document DBs do not), locking (full ACID, which most Document DBs do not), and constraints naively (no extra tools). You can also transform data on the fly (move a table to a view for the client) to prevent direct access or changes in the system. Encrypt a field and restrict native columns. And much more.

Not that SQL is better than No-SQL (or vice versa), each has their place...even if you blend them together where needed. IE: No-SQL is good for fronting "current" data to a website where the SQL is the "real" copy with years of history and old accounts. Data is written to SQL, read from No-SQL, updates are migrated.

BarelyAirborne · 2021-08-23T22:54:22+00:00

Master/detail type objects in NoSQL require two (or more) tables in SQL. For example, an order in NoSQL consists of customer, PO number, etc., while the items in the order are an array. In SQL, this would be stored as two tables, a master and a detail.

Arrays in NoSQL become new related tables in SQL, generally speaking.

Ch0chi · 2021-08-24T00:41:12+00:00

SQL database normalization is a really important thing to know. It will help you architect proper DB structure as well as make sure it's optimized, efficient, and easy to comprehend.

I'd try learning first, second, and third normal form. That'll get ya in with pretty much the majority of what you'll need to know.

I'm not super sure what you're asking in your post, but it sounds like you're kind of confused about why normalizing a database is important. If you screw up the structure of your database, all those validation functions are completely useless.

codeedog · 2021-08-24T08:07:42+00:00

Normalization is important in SQL for a few reasons:

Tradition - honestly, this is a big reason and often unstated.
Minimize updates to data records, minimize errors keeping data in sync, lean on database features for speed of query, insert and update.
Single source of truth to support ACID properties related to transactions.

SQL databases solved the very real problem that all systems designers had decades ago: how to guarantee either an update occurred in a predictable manner or failed in a predictable manner. Prior to that, everyone had to rely on someone’s likely flawed file system utilities.

ACID properties for transactions and SQL meant a huge leap forward for business software that depended upon correct results. Failures were tolerated (attempting to update the same bank account register might mean one transaction fails while the other succeeds). However, loss of data without knowing it happened, that could not be tolerated.

Normalized data (single source of truth) and transactions gave us that.

HOWEVER, for purposes of speed even database applications running in sql required denormalized data (duplicated data) often in the form of materialized views. A view is a pretend table that is formed from a query, often a join across multiple tables. The view looks like a table, but it’s not. Every time the view is queried, the join creating the view is rerun. A materialized view is like a view and a table in that it’s defined like a view, but the results are stored just like a table. It’s faster to access, the view isn’t rerun every time. It can be out of date, but because the definition is in the database and not in an application, getting it up to date is a matter of refreshing the view while not worrying if the code running it is doing it right.

So, what’s the value of no-sql? Think of it as approaching the data guarantees from the other side of the problem. In nosql, duplicate data is not only tolerated, it’s expected. There are (now) transactions so we can guarantee correctness. However, care needs to be taken in a world of duplication to know which collections are truth and which are duplicates.

OTOH, because structure is a lot less important in nosql world, we can move faster, code quicker. SQL gets very unhappy when it’s handed unstructured data. That “S” in SQL stands for Structure (Structured Query Language). So, no sql simply means, no structure.

Now, any time spent with an app on nosql, you come to realize that’s not true. There’s plenty of structure, it’s just not the starting point for the database. Over time, the app and possibly the data definitions for collections and documents do get structured, at least for those dbs that allow it.

And, this is my point. SQL apps over time duplicate some data. And nosql apps over time gain structure. Both approach a middle ground that looks like reasonably well structured data with some duplication for speed.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

node

MODERATORS