Normalization
-
Cpichols wrote:
Help me out here; where are these databases and what uses do people make of them?
The sad answers to your questions are "everywhere" and "a mess". Visualize a purchasing system where general customer information is free text. Further visualize trying to produce a sales report where a customer name may be spelled "ABC Trucking LLC", "ABC Trucking", "ABC Trucking Inc", and to make it really entertaining, " A B C Trucking " or " ABC Trucking" (application doesn't trim input and white space is significant). On the plus side, working with a system like this will make anyone an expert in regex ... :laugh:
Okay then. While I do love a good regex puzzle, I'm thinking that maybe it's best to use selection menus built from a tight db. The db I work with was brilliantly designed, but does lack the level of normalization (ooh! look at me using this word in this new-for-me way! lol) that I would prefer, but it's pretty close for all that. I'd add one more layer, iah.
-
How many here normalize their databases?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
A wise man told me once: normalize till it hurts, then denormalize until it works. My app are mostly transactional and I don't see any other way then to have denormalized data.
-
How many here normalize their databases?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
I usually do not unless it is specifically requested by the project/boss because I tend to favor performance over conformance to normalization. To avoid data duplication or making a messy DB, I rely on my instinct and experience designing databases. But my DBs (for production) are usually built in layers and normalization only applies to the bottom (data) layer. On top of that are usually performance layers like "history request cache" for each client, cached queries (for full/partial queries that are required by other queries, usually per client), filters for queries (usually temporary tables with partial data), etc. And, before someone asks, yes those layers are implemented in the database (using SQL) where data is readily available and protected. And time is not wasted transferring data to some app while, in the process, creating a vulnerability in the database.
-
I didn't take it as an attack, no worries. I figured I just needed to clarify. I learned DBs in the .com boom days when it was a free for all. I attempted to impose some order on the mess. All I got for my efforts typically was put in charge of databases - something i didn't want to be in charge of. And then there was the time when I worked with a self taught developer who built out an entire ecommerce platform, front and back end, including a jquery-like (but *not jquery :~ :mad:) engine for the front end. Rather than doing JOINs in the database he was doing them in PHP on the webserver. I taught him SQL. Then I quit.
Real programmers use butterflies
honey the codewitch wrote:
Rather than doing JOINs in the database he was doing them in PHP on the webserver
I had a colleague like that once. He was transferring two 100G entries tables (+-50 columns each) to PHP to do joins using for loops. It was killing the server and taking almost a week to process. The boss only noticed when he requested a new, more powerful server (our server was brand new). Boss told me to converted it to SQL and it started taking less than an hour to do the same join ;P That colleague quit the project soon after that.
-
How many here normalize their databases?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
I almost always use third normal form to begin with and keep it that way unless there are performance problems.
-
How many here normalize their databases?
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
During design, I normalize to the optimum end, the denormalize where necessary for performance. I've been following that process since I started in databases back in the late 80s. Of equal importance is knowing what to index on, and how to keep indexing and PK-FK relationships from getting out of hand to where performance and supportability become a problem.
-
honey the codewitch wrote:
Rather than doing JOINs in the database he was doing them in PHP on the webserver
I had a colleague like that once. He was transferring two 100G entries tables (+-50 columns each) to PHP to do joins using for loops. It was killing the server and taking almost a week to process. The boss only noticed when he requested a new, more powerful server (our server was brand new). Boss told me to converted it to SQL and it started taking less than an hour to do the same join ;P That colleague quit the project soon after that.
ElectronProgrammer wrote:
That colleague quit the project soon after that.
Did he fall, or was he pushed? :-\
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
-
That's true, and given the amount of space to work with that we have these days, and how good databases are at doing batch data conversion and such, there really isn't a good reason to avoid redundant data if it makes things easier or more efficient. You just have to be careful not to go crazy with it, and you have to keep in mind that it can make your database more "brittle" because its more fields that need to be proofed/validated for correctness. Keeping redundant data in sync is a chore unto itself. But yes yes yes to this. Redundant data is okay, when it serves a purpose, often for performance or integration purposes it is The Right Way(TM) to do things. And if I'm wrong then I got paid a whole lot of money to be wrong, and a lot of people happy with how wrong I was. :laugh:
Real programmers use butterflies
But don't forget what Edsger Dijstra once said: If you say the same thing twice, you will contradict yourself.
-
ElectronProgrammer wrote:
That colleague quit the project soon after that.
Did he fall, or was he pushed? :-\
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
He fell. It wasn't his only mistake. I tried to give him an hand but he took the arm to harm (he had a PhD and I had a licenciate degree*). I would rather have him there than ending overworked and underpaid as I did. I guess the corporate reality train was to heavy for him :sigh: * That degree is one below masters and one above bachelor in pre-bologna process which was my case
-
But don't forget what Edsger Dijstra once said: If you say the same thing twice, you will contradict yourself.
Not if you say it properly. *hides*
Real programmers use butterflies
-
Eddy Vluggen wrote:
Math.
NoMATH (for clarity, Not Only Math) :rolleyes: Actually, that math was failing with big enough datasets, which is why NoSQL was (re-)invented. So the right way to do things went from "normalize everything" to "denormalize a good bunch." We've seen the opposite happen to peanut butter.
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
When you're talking about redundant storage, are you referring to data aggregated from single points of origin or an uncontrolled mess?
Wrong is evil and must be defeated. - Jeff Ello
And some redundancy is not really redundant, it is the historical context that needs to be saved. The invoice earlier is a good example. If the company changes its name or moves it’s HQ, you do not want the invoice to update. The best part is when there are M/A and divestitures among your client base. At a high enough altitude, it looks like a game of Life cellular generational display.
-
I find No SQL kind of funny as it is one of those cyclic patterns. In the same vein with centralized versus distributed.
Before SQL, which didn't become popular until the 80's, there was only not SQL :rolleyes:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
