Re: design question
Date: Mon, 8 Sep 2008 05:39:25 -0700 (PDT)
On Sep 5, 10:51 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Chris Seidel wrote:
> > DA Morgan wrote:
> >> "Best" depends on what you are going to do with it other than store
> >> it.
> > Query it. I have to find objects fast based on the field data (=, >, <,
> > between, like)
> >> "Best" depends on whether you require it reconstituted as XML at some
> >> point in the future.
> > No XML needed.
> >> "Best" depends, to a lesser extent, on your version which you don't
> >> state (3 decimal places).
> > A stores all as string, no problem.
> > For B a new column will be there numeric(x,y) with y >= 3.
> > But for 99 % of the objects 2 decimal places will be ok.
> >> My preference for reasons that range from performance to reporting to
> >> storage to relational theory is to shred XML into its constituent
> >> elements storing each with its proper data type.
> > So you would prefer A?
> > What about the many null columns in this approach?
> Most things have NULL columns ... what's the issue?
> If you don't store numbers as numbers you can not do math. If you do
> not store dates as dates you can not do date math. Storing everything
> as strings made sense on mainframes 20 years ago. It has no place in a
> modern RDBMS.
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
> - Show quoted text -
>> Storing everything as strings made sense on mainframes 20 years ago <<
I suspect Daniel was just trying to make a point but I worked on mainframes 20 years ago and when it came to storing numeric data in your ISAM/VSAM data files or IMS or IDMS database you wanted to use a numeric storage definition for numeric data: packed decimal, integer, or float.
Dates were another issue as every shop had its own date formatting and manipulation routines. We mostly used what was called Julian date format, but which was really YYDDD.
Input files were generally all character data likely with over-punches for numeric characters but otherwise you still tried to match the data to the type.
IMHO -- Mark D Powell -- Received on Mon Sep 08 2008 - 07:39:25 CDT