Re: Help, my developers are killing me with varchar2(4000)

"phil_herring_at_yahoo.com.au" <phil_herring_at_yahoo.com.au> writes:

> On Aug 11, 11:21 pm, Wolfram Roesler <w..._at_spam.la> wrote:
>
>> Of course it is bad style, but the question why this actualls IS bad (i. e.
>> what bad things will happen with it) still hasn't been answered in this
>> discussion.
>
> The difference is one of outlook. Many application developers take the
> position that the DB is part of the application; it's just a bucket
> you can toss things into and fish out later on. Almost everybody else
> takes the view that the DB is a separate thing, and the app is just
> one of a number of tools that use it. This latter group includes the
> DBA, the bean counters who prowl the data, the managers who read the
> reports from the bean counters, and pretty much all the other
> stakeholders who depend on access to accurate data to do their jobs.
>
> These people generally take the view that the database should contain
> as much semantic information as possible about the data that it
> contains, so making all your data long strings is heading in the
> opposite direction to what you want. Rather, you want to be as
> specific as possible at all times. That means if you're designing a
> database and you don't know what's going in a particular column, you
> find out. Don't be lazy and just say "oh, let's make it a big string
> and code around that"; find out what it is. If it's a date, use a
> date. If it's an integer, use an integer. If it's a PDF, use a BLOB.
> Most modern DBMSs have enough types to satisfy just about any
> requirement. Using them appropriately is a good thing. It conveys
> useful information. It also catches a whole raft of programming
> errors, such as when people try to stuff a string into a date column.
> If the column is correctly typed, that can't happen, but if it's a
> string, it will.
>
> Theere are also issues of scalability, both in data size and number of
> users. The world gets a lot more complex when your database leaves
> your test environment, where it had a few dozen rows per table and
> three or four users, and suddenly has a few million rows and a few
> hundred users. In such circumstances, I have seen few implementations
> of the "big bucket" data model survive for long. Most fail pretty
> quickly, because you need to do everything right if you want to scale
> up substantially.
>
> -- Phil

I second that. The database isn't supposed to just be a bit bucket. It is supposed to be a representation of the underlying data model. As such, it provides, or at least can provide, a lot of valuable maintenance information and error checking and 'free' data integrity management through the use of the right types, various constraints (including maximum data sizes) etc.

In a reasonable number of years doing database development, I've not yet come across any character type that I couldn't identify a maximum length for that I would want to process/manipulate as a varchar2 (or similar types in other products). maybe I've been lucky, but by the time I've encountered something that wouldn't fit inside the 4k limit, it usually meant it wasn't something I needed to manipulate in a string like manner, but instead treated as just a big blob of something. Of course, the landscape is changing a bit with XML, but Oracle has alternative types and support for XML anyway.

Tim

-- 
tcross (at) rapttech dot com dot au