|
There - I said it! 
For almost 40 years, (apart from the odd dalliance with MySQL, SQL Server & Oracle), I have worked with databases that impose no such restrictions. So maybe I'm biased.
Here's my argument for removing the database schema 'straight-jacket':
- ASAP Data Validation. 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.
- Flexibility. It provides flexibility at no cost. A traditional RDBMS has a fixed data model, (defined by the schema), and will need effort every time it needs changing - and, these days, that responsibility is quite probably with another team: DBA's. If nothing else, this will cost time.
- Disk space. 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!
P.S. I'm now going out until the flames die down! 
|
|
|
|
|
OK, so you've stored your dates in your database as unlimited-length strings. With varying levels of "validation" on the front-end.
Now write a report based on a date range, given your table contains values such as "20190314", "1st May 2020", "32nd February 2990", "Third Sunday after Whitsun 21997", etc. 
And that's before you notice the significantly increased space it takes to store a string representation of a date over a proper date column.
Using proper data types in the database is precisely the kind of "straight-jacket" you want.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well I could validate at entry to ensure it's a consistent format - but, in practice, with the database I use, dates are stored as the number of days since 31/12/67. So, for dates since then, we are holding 1-5 digits - and for dates before that, there'll be a "-" in front.
If you tell the query language, (which includes an implementation of SQL), that the column is a date, it will figure out how to sort and select on that column. But that isn't enforcing any restrictions on how data is stored - it's just to help us use it when we need it - e.g. for a report.
|
|
|
|
|
If you're telling the data definition language a column is a date, why do you care how it's stored?
|
|
|
|
|
|
What you are bringing up is a foundational challenge of the Software Developer: where is the proper place for database-related logic. IE - How much does application do versus what the DBMS does?
ASAP Data Validation
You are codifying rules into your software that some else has to learn later on when/if a new application is built on top of the data.
If that is absolutely never going to happen (the data is only used by this one app) then you are correct, keep all the rules in the application.
But, what if later on the data in the database is going to be kept and a new App is being built, but now there are length constraints that you have to find all through the code? That could be very difficult when a dev down the road could just look at the length of the target field in the DB.
Flexibility
You said, "A traditional RDBMS has a fixed data model, (defined by the schema), and will need effort every time it needs changing"
But now this makes it sound like, "well, we'll create one big wide table with fields named Field1...Field200 and we'll just use it like a bucket. Make everything a huge varchar field."
But that can make the DB extremely slow also since fields will not be able to be indexed as easily.
Use Each Tool For It's Best Purpose
I like the balance of letting each tool do it's own work.
Here's what I mean by that:
1) allow the DB side handle lengths by implementing the built-in type system & field size constraints.
2) allow the software side (application) to do the programmatic types of things and much of data validation (insuring phone number is valid format).
3) Another good example is not using triggers. Triggers are like secrets that fire in the DB which later do some programmatic thing that can be very invisible to the developer. But this is programmatic type of code that should probably be in the app --- which helps you remember that the app is where data manipulation really occurs.
On your point, as a dev myself I can definitely see how you'd want as much _control_ of how the data is used in the application layer since we don't want to depend upon constraints that the DB Admin puts upon us. It just makes our lives more difficult & less flexible when we need to fix a thing that is happening in production.
There is no exact answer to this, of course, it is a ebb & flow type of thing & related to each shop and how they do it.
|
|
|
|
|
raddevus wrote: You said, "A traditional RDBMS has a fixed data model, (defined by the schema), and will need effort every time it needs changing"
But now this makes it sound like, "well, we'll create one big wide table with fields named Field1...Field200 and we'll just use it like a bucket. Make everything a huge varchar field."
That reminded me of a team project I worked on back in 2000 when one of the developers thought he had the best idea for a database for our new project. His solution was one table with ID, Title, and Value. That's it. Everything and I mean everything would be put into that table. "Easy Peasy" right? What a nightmare that was. 
"When you are dead, you won't even know that you are dead. It's a pain only felt by others; same thing when you are stupid."
Ignorant - An individual without knowledge, but is willing to learn.
Stupid - An individual without knowledge and is incapable of learning.
Idiot - An individual without knowledge and allows social media to do the thinking for them.
modified 19-Nov-21 21:01pm.
|
|
|
|
|
Donathan Hutchings wrote: Everything and I mean everything would be put into that table. "Easy Peasy" right? What a nightmare that was.
Yep, I've seen the same thing. It's one of those ideas that for like 10 seconds seems like the Greatest Idea Ever!!! Then, for the years afterward it is the most terrible thing ever.
|
|
|
|
|
Yup. That idea was abandoned when he had to write the first search query. 
"When you are dead, you won't even know that you are dead. It's a pain only felt by others; same thing when you are stupid."
Ignorant - An individual without knowledge, but is willing to learn.
Stupid - An individual without knowledge and is incapable of learning.
Idiot - An individual without knowledge and allows social media to do the thinking for them.
modified 19-Nov-21 21:01pm.
|
|
|
|
|
For completeness, this is called the Entity-attribute-value model and it has its place in the design tools drawer. To say that you should use it for everything is as silly as to say that you should use it for nothing.
Mircea
|
|
|
|
|
I completely agree, but again, this was in 2000 when asp, vbscript, and Javascript was king. I use it all of the time as lookup lists.
"When you are dead, you won't even know that you are dead. It's a pain only felt by others; same thing when you are stupid."
Ignorant - An individual without knowledge, but is willing to learn.
Stupid - An individual without knowledge and is incapable of learning.
Idiot - An individual without knowledge and allows social media to do the thinking for them.
modified 19-Nov-21 21:01pm.
|
|
|
|
|
raddevus wrote: well, we'll create one big wide table with fields named Field1...Field200 and we'll just use it like a bucket. Make everything a huge varchar field
I've once had to fix a table like this, what a nightmare it was
|
|
|
|
|
|
Especially when you realized that the fields in the application didn't end up in the same columns, it depended on whether they had data or not. 
|
|
|
|
|
Quote: 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
|
|
|
|
|
den2k88 wrote: fixed column size >may< help DB performances a lot
Allowing the db to access a field in a record by position can enhance performance a lot.
But only if it doesn't kill it with memory requirements.
My rule of thumb is to always put varchars last in a record.
|
|
|
|
|
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[2000] 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.
|
|
|
|
|
I think you should do as much validation/checking at every level: app and DB both.
Also, use custom app and dbtypes as much as possible.
No app code should use String as a type for domain data, same for the DB.
When the AddressLine type changes in your code, that means that the same needs to happen to the AddressLine type in the DB.
|
|
|
|
|
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... 
|
|
|
|
|
5teveH wrote: 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
|
|
|
|
|
David Crow wrote: And what happens when the next app comes along that needs to interact with such a database/table?
Exactly this!
|
|
|
|
|
Organization provides meaning to the data. A model. A "dynamic bag" is just that: a bag.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
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
|
|
|
|
|
#realJSOP wrote: How is that "fixed length"?
Agreed, it is variable length - up to a point. But if you try to insert an eleven-character string into a VARCHAR(10) column, SQL is going to bork!
But my main point was the restrictions being implemented by the database - rather than in the code.
modified 28-Aug-21 10:02am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
