RE: DWH varchar2(4000)
Date: Wed, 24 Dec 2014 14:02:43 -0500
Message-ID: <03b701d01fac$341ce810$9c56b830$_at_rsiz.com>
" No, it was taught to me in the early 90s as part of a formal Data Modelling course. I am pretty sure it is/was standard practise in the old mainframe databases (IMS? IDMS?, it's been a long time :) It is not database design, it is data modelling."
- Your argument seems to be that because it was taught, therefore it is valid. This is a classic example of Argumentum ad verecundiam (or appeal to authority), which shifts the argument away from the topic at hand to whether the authority can be trusted on some subject. Without citation of the authority no inference can be made about the likelihood of it being correct. With citation of someone highly respected at most it improves to some indirect indication that it is worth evaluating for correctness. For example if you were to cite Chris Date and I thought you correctly understood what he had said, I would carefully evaluate and test something before rejecting or accepting it, while if you cited some lesser light I might reject it out of hand.
- The older the technology the more precious were space and size in consideration of everything (unsupported claim from my experience). Of course CPUs were also slower, so it was indeed valid to consider space for time trade-offs (sorts being a classic, about which Knuth wrote a fair amount). When picture generation routines were required for each size of text object to generate code to be swallowed by the compiler instead of hand writing everything, there was a substantial value to prototyping in a small number of allowed sizes. Regarding database modelling for modern systems it would be peachy to use a generous max when domain set limitations are unclear IF it were not the case there are known side effects (that are significantly negative) when a row can not a priori be known to fit in a single block. (That's no appeal to authority; test results and a recipe book for you to repeat the tests yourself are conveniently in this very thread).
Good luck.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Dave Morgan
Sent: Wednesday, December 24, 2014 11:17 AM
To: Oracle-L
Subject: Re: DWH varchar2(4000)
On 12/24/2014 06:43 AM, Mercadante, Thomas F (ITS) wrote:
> Dave,
> I find this an interesting comment. But I worry about data integrity. If
we only allow varchar of 255, or 2000, then what are the possible values of
that column? Where is the definition of that column?
> to me, this is the lazy way out - don't perform full analysis of what the
business process is and just store everything without a care.
The exception is fixed length fields. Other than that if you waste more than
15 seconds on determining field size you are wasting time and effort.
You are also wasting the time and effort of the people who have have to
review the code. They have to re-evaluate all the custom sizes and determine
why they are custom.
And of course full and complete documentation is provided. And of course the
modeller is never wrong.
Sorry, tell someone who cares, I have a database to run.
As for analysis, why don't you provide the data and reasons for your judgement that proper analysis was not performed? Just as I have provided the reasons for the choices.
And relying on the database for data validation is not wise. Bad data is entered regardless of field size. Constraints are the last line of defence, not the first.
>
> Is this a new global approach to database design?
No, it was taught to me in the early 90s as part of a formal Data Modelling course. I am pretty sure it is/was standard practise in the old mainframe databases (IMS? IDMS?, it's been a long time :) It is not database design, it is data modelling.
YMMV
Dave
-- Dave Morgan Senior Consultant, 1001111 Alberta Limited dave.morgan_at_1001111.com 403 399 2442 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 24 2014 - 20:02:43 CET