The Lounge is rated Safe For Work. If you're about to post something inappropriate for a shared office environment, then don't post it. No ads, no abuse, and no programming questions. Trolling, (political, climate, religious or whatever) will result in your account being removed.
Defining a column size to accommodate the maximum possible length is a huge waste of space in a large table - particularly when there are probably dozens of those columns. For instance: a simple postal address table will need, at least, 5 x CHAR(50) address fields, plus a post/zip code field. And how many addresses actually use all of that space? Zero!
This is the only piece I don't fully agree with, fixed column size help DB performances a lot. So if your architecture needs performances, you might want fixed data sizes. Especially if those data need to be sent to native code, as moving a single contiguous block of data is way easier and faster than serializing/deserializing.
GCS d--(d-) s-/++ a C++++ U+++ P- L+@ E-- W++ N+ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
So...you want the UI to take care of length restrictions, while the DB backend remains wide open?
What happens when a hacker finds a way to get data in without using the UI that imposes the restrictions, and starts shoveling tons of data until you run out of disk space? And uses data types that the frontend subsequently reads back doesn't expect?
Belt and suspenders have a specific meaning in IT, there's a reason it's generally a good idea.
Let's start with your claims, which are all invalid with modern database engines.
Data Validation - While I agree that applications should be doing this, the reality is the database engine needs to do this as well to ensure that the data being entered matches what the database designer anticipated. Called procedures not validating parameters are the number two source of security flaws in software. In databases this maps to the database engine enforcing data validation prior to storing the data in the database.
Flexibility - Most data doesn't need this. Dates, times, numerics, etc., all have defined ranges that support a large number of possible values. No, these values aren't infinite but they have sufficient range for 99.999% of all database applications. Some values such as binary, by their very nature, have only a small number of enumerable values.
Disk space - The days of a string or binary value taking the entire allocated space are over. All modern database engines only store sufficient space to contain the value. For example, if the data type is STRING and the string being stored is "Hello, World!", the the database engine will only store a count and the 13 characters of the string.
Now let's look at performance. For databases of any size to be fast, they must index the data. In fixed size fields the indexing is extremely easy and can be done relatively quickly, especially when using modified balanced red/black indexes with scanning a small number of values between them. This type of index hits at most two memory and disk pages to split a node and most of the time only a single memory or disk page to add a value to a node. To index variable length fields the database engine would have to do a full text scan of each field as it's entered/updated and update multiple locations in the index. This is a far more expensive operation and is guaranteed to hit multiple, unpredictable memory and disk pages, causing a lot more disk and memory thrashing to complete the insert/update operation.
The bottom line is databases and database designers need to be able to identify datatypes for security, disk space, and performance reasons. None of these impact flexibility unless the database isn't well designed.
Well yes, and no.
I get what you're saying, and so do a plethora of NoSQL database developers.
If only one app or service communicates with a database then the app should know how that database stores its data.
CRUD on the database should always go through that specific app or service.
However, this is a utopia (although one we're getting at).
Getting a second application to work with that database could be problematic, as this application does not know how the data is stored and may end up being not be 100% compatible with the first application.
The reality is often that multiple applications communicate with the same database, people do ad-hoc database queries and updates, reporting tools communicate with the database...
Everyone and their grandmother communicate with the same database.
In that case it's really convenient that the database enforces a schema that ensures your other applications never break.
Because someone is going to screw up your data sooner or later.
In fact, I've already seen that happen lots of times in SQL databases, because a certain value had to be in a specific range or some such.
That doesn't mean you shouldn't also validate in your application though, you don't want to go out with an (unhandled) SqlException, but with a user friendly message.
I've worked with MongoDB, which is schemaless, and it's really awesome.
So I agree with you, and then I don't, but then I do...
It puts the sole responsibility for data validation exactly where it should be: with the developer. The best place to catch bad data, is at the point of entry - before it gets anywhere near a database. That applies to user input and data feeds. If you are going to validate early, (and properly), there's no need to have a rigid database schema.
And what happens when the next app comes along that needs to interact with such a database/table? What about a mobile app? That just looks like a rewrite of code each and every time.
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
In Sql Server, a varchar column only takes two bytes when it's empty, and then only consumes the space required by its value when it's populated. How is that "fixed length"?
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
Anything that is unrelated to elephants is irrelephant Anonymous - The problem with quotes on the internet is that you can never tell if they're genuine Winston Churchill, 1944 - Never argue with a fool. Onlookers may not be able to tell the difference. Mark Twain
Last week's SOTW reminded me of this one, mainly because of the beautiful piano play.
I actually learned about this song back in 2013 from a Britain's Got Talent act: Attraction - Shadow act - Read all about it. Britain's Got Talent 2013 - YouTube[^]
The song was originally a rap song, with lyrics by Emeli Sandé, but Emeli released her own (far better) version of the song, which would be pt. III (there's also a pt. II, but I don't know it).
The song has a message, I guess everyone matters and deserves to be heard.
Not sure I agree, just turn on the news and you'll see plenty of people who deserve to be silenced, but maybe those aren't the people she sings about.
One thing is for sure, Emeli's voice may be heard
That link gives me a "Video not available"...
They're both the same video (or at least same title) from the same official channel...
I never understood the video blocking in certain countries (other than copyrights).
Would've loved that one some 15 years ago, now it's mostly nostalgia
I still like to listen to Arch Enemy's Wages of Sin and Anthems of Rebellion from time to time (but haven't heard a single album after that).