Imposing data type and length restrictions at the database level is stupid!
-
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.
Wrong is evil and must be defeated. - Jeff Ello
-
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
Wrong is evil and must be defeated. - Jeff Ello
-
Jörgen Andersson wrote:
I've once had to fix a table like this, what a nightmare it was
:thumbsup::thumbsup::thumbsup: Absolutely. Those of us who have experienced it know the terrible horror that idea is.
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. X|
Wrong is evil and must be defeated. - Jeff Ello
-
There - I said it! :wtf: 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! :)
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 -
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.
